sql server字符串与表格互相转换的函数和方法
A.将逗号分隔的字符串分拆成表格的方法：
拆分的方法有很多，临时表.SUBSTRING ...但都不如XML来得清凉爽快
declare @tempstr varchar(500)
set @tempstr='54,57,55,56,59'
declare @Xmlstr xml
set @Xmlstr=Co nVERT(xml,'' + REPLACE(@tempstr, ',', ' ') + '
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
( www.2cto.com
[sid] INT IDENTITY(1, 1) ,
[svalue] nvarchar(max)
)
as
begin
--把字串转成XML
declare @Tempxml xml;
set @Tempxml=Co nVERT(xml,''+REPLACE(@SplitStr,@Separator,' ')+' ')
--把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 ( www.2cto.com
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')),'',''),' |
',',')
from @T1 t group by id
作者 51ak