sqlserver字符串拆分(split)方法汇总

--方法0：动态SQL法
declare @s varchar(100),@sql varchar(1000)
set @s='1,2,3,4,5,6,7,8,9,10'
set @sql='select col='''+ replace(@s,',''' union all select ''')+''''
PRINT @sql
exec (@sql)

?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN',N'IF',N'TF'))
drop function [dbo].[f_splitSTR]
GO
--方法1：循环截取法
CREATE FUNCTION f_splitSTR(
@s?? varchar(8000),?? --待分拆的字符串
@split varchar(10)???? --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
?DECLARE @splitlen int
?SET @splitlen=LEN(@split+'a')-2
?WHILE CHARINDEX(@split,@s)>0
?BEGIN
??INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
??SET @s=STUFF(@s,1,@s)+@splitlen,'')
?END
?INSERT @re VALUES(@s)
?RETURN
END
GO

?INSERT @re SELECT SUBSTRING(@s,ID,@s+@split,ID)-ID)
?FROM @t
?WHERE ID<=LEN(@s+'a')?
??AND CHARINDEX(@split,@split+@s,ID)=ID
?RETURN
END
GO

--方法4：利用sql server2005的OUTER APPLY

CREATE FUNCTION [dbo].[ufn_SplitStringToTable] ( ? @str VARCHAR(MAX),? @split VARCHAR(10) ) RETURNS TABLE ??? AS? RETURN ??? ( SELECT??? B.id ????? FROM????? ( SELECT??? [value] = CONVERT(XML,'<v>' + REPLACE(@str,@split,'</v><v>') ??????????????????????????? + '</v>') ??????????????? ) A ????? OUTER APPLY ( SELECT? id = N.v.value('.','varchar(100)') ??????????????????? FROM??? A.[value].nodes('/v') N ( v ) ????????????????? ) B ??? )

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!