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