DECLARE @STR NVARCHAR(200) SET @STR = 'NAME=YUNA,AGE=31,Fan=Music,Address=XiAn' set @str=right(@str,len(@str)-charindex('Fan',@str)+1) select substring(@str,charindex('=',@str)+1,charindex(',',@str)-charindex('=',@str)-1) /*
---------------- Music(所影响的行数为 1 行)*/
你这样写,如果获取address的值,就错误啊
这样写不管你换成NAME,AGE,Fan,Address这几个任意一个都OK的DECLARE @STR NVARCHAR(200) SET @STR = 'NAME=YUNA,AGE=31,Fan=Music,Address=XiAn' set @str=','+@str+',' set @str=right(@str,len(@str)-charindex('Address',@str)+1) select substring(@str,charindex('=',@str)+1,charindex(',',@str)-charindex('=',@str)-1)
DECLARE @STR NVARCHAR(1000),@PART varchar(30) SET @STR = 'NAME=YUNA,AGE=31,Fan=Music,Address=XiAn'SET @PART='Fan' --替换该部分即可select left(str,charindex(',',str)-1) from (select stuff(@STR,1,charindex(@PART+'=',@STR)+LEN(@PART),'')+',' as str) t
我的 @STR 实际上是 NTEXT 备注字段,不能用 STUFF 啊
你的有错误啊例如我的名字是 YUNAAGE 怎么办? DECLARE @STR NVARCHAR(200) SET @STR = 'NAME=YUNAAGE,AGE=31,Fan=Music,Address=XiAn' set @str=','+@str+',' set @str=right(@str,len(@str)-charindex('Age',@str)+1) --select substring(@str,charindex('=',@str)+1,charindex(',',@str)-charindex('=',@str)-1) print @str
declare @str nvarchar(1000) SET @STR = 'NAME=YUNAFan,AGE=31,Fan=Music,Address=XiAn' declare @find nvarchar(30) set @find='Fan'select substring( substring(@str, case when patindex('%'+@find+'=%',@str)=0 then datalength(@str) else patindex('%'+@find+'=%',@str) end+len(@find)+1, datalength(@str) ) ,1 , case when patindex('%,%',substring(@str,patindex('%'+@find+'=%',@str)+len(@find)+1, datalength(@str) ) )=0 then datalength(@str) else patindex('%,%', substring(@str,patindex('%'+@find+'=%',@str)+len(@find)+1, datalength(@str) ))-1 end )
SET @STR = 'NAME=YUNA,AGE=31,Fan=Music,Address=XiAn'
set @str=right(@str,len(@str)-charindex('Fan',@str)+1)
select substring(@str,charindex('=',@str)+1,charindex(',',@str)-charindex('=',@str)-1)
/*
----------------
Music(所影响的行数为 1 行)*/
你这样写,如果获取address的值,就错误啊
SET @STR = 'NAME=YUNA,AGE=31,Fan=Music,Address=XiAn'
set @str=','+@str+','
set @str=right(@str,len(@str)-charindex('Address',@str)+1)
select substring(@str,charindex('=',@str)+1,charindex(',',@str)-charindex('=',@str)-1)
SET @STR = 'NAME=YUNA,AGE=31,Fan=Music,Address=XiAn'SET @PART='Fan' --替换该部分即可select
left(str,charindex(',',str)-1)
from
(select stuff(@STR,1,charindex(@PART+'=',@STR)+LEN(@PART),'')+',' as str) t
你的有错误啊例如我的名字是 YUNAAGE 怎么办?
DECLARE @STR NVARCHAR(200)
SET @STR = 'NAME=YUNAAGE,AGE=31,Fan=Music,Address=XiAn'
set @str=','+@str+','
set @str=right(@str,len(@str)-charindex('Age',@str)+1)
--select substring(@str,charindex('=',@str)+1,charindex(',',@str)-charindex('=',@str)-1)
print @str
sql2005就直接convert(nvarchar(max),字段)
declare @str nvarchar(1000)
SET @STR = 'NAME=YUNAFan,AGE=31,Fan=Music,Address=XiAn' declare @find nvarchar(30)
set @find='Fan'select substring( substring(@str, case when patindex('%'+@find+'=%',@str)=0 then datalength(@str) else patindex('%'+@find+'=%',@str) end+len(@find)+1, datalength(@str) )
,1
, case when patindex('%,%',substring(@str,patindex('%'+@find+'=%',@str)+len(@find)+1, datalength(@str) ) )=0
then datalength(@str)
else patindex('%,%', substring(@str,patindex('%'+@find+'=%',@str)+len(@find)+1, datalength(@str) ))-1
end
)