sqlserver字符串与表格互相转换的函数和方法
A.将逗号分隔的字符串分拆成表格的方法：
拆分的方法有很多，临时表.SUBSTRING ...但都不如XML来得清凉爽快
declare @tempstr varchar(500) set @tempstr='54,57,55,56,59'
declare @Xmlstr xml set @Xmlstr=Co nVERT(xml,'<root><v>' + REPLACE(@tempstr, ',', '</v><v>') + '</v></root>') SELECT ids=N.v.value('.', 'int') FROM @Xmlstr.nodes('/root/v') N(v)
写成函数如下：
create FUNCTIo n [dbo].[ufn_sys_Str2Table] ( @SplitStr nvarchar(max), @Separator nvarchar(10) = ',' ) RETURNS @ResultTable TABLE ( [sid] INT IDENTITY(1, 1) , [svalue] nvarchar(max) ) as begin --把字串转成XML declare @Tempxml xml; set @Tempxml=Co nVERT(xml,'<d>'+REPLACE(@SplitStr,@Separator,'</d><d>')+'</d>') --把XML转成表 insert into @ResultTable ([svalue]) select co nvert(nvarchar(max),Tb.co.query('data(.)') ) as rvalue from @Tempxml.nodes('/d') Tb(co) RETURN ; end
B.统计一个字符串在另一个字符串出现的次数的函数
CREATE FUNCTIo n [dbo].[fnQueryCharCountFromString] ( @Str NVARCHAR(MAX), --待查找字符串 @Spilt CHAR(1) --需查找的字符 ) RETURNS INT AS BEGIN DECLARE @Count INT SELECT @Count = COUNT(*) FROM ( SELECT SUBSTRING(s,n,1) AS split FROM ( SELECT @Str AS s,n FROM dbo.nums WHERE n < LEN(@Str)) D ) A WHERE A.split = @Spilt RETURN @Count;
END
C.把表格拼成字符串，多伴生使用于GROUP BY
--假定要聚合的字段是id ,要统计的字段是tname --select a.tname from @T1 a for xml path('row')
select id,REPLACE(replace((select a.tname from @T1 a where a.id=t.id for xml path('row')),'<row><tname>',''),'</tname></row>',',') from @T1 t group by id
