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

发布时间：2020-12-12 13:00:42 所属栏目：MsSql教程来源：网络整理

导读：--方法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 = obj

--方法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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN',N'TF'))
drop function [dbo].[f_splitSTR]
GO
--方法2：使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s?? varchar(8000),? --待分拆的字符串
@split varchar(10)???? --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
?--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
?DECLARE @t TABLE(ID int IDENTITY,b bit)
?INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

?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

drop function [dbo].[f_splitSTR]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
drop table [dbo].[tb_splitSTR]
--方法3：使用永久性分拆辅助表法
--字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s???? varchar(8000),? --待分拆的字符串
@split? varchar(10)???? --数据分隔符
)RETURNS TABLE
AS
RETURN(
?SELECT col=CAST(SUBSTRING(@s,ID)-ID) as varchar(100))
?FROM tb_splitSTR
?WHERE ID<=LEN(@s+'a')?
??AND CHARINDEX(@split,ID)=ID)
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
??? )

备注说明：

方法4必须在sql server2005下才可以运行

（编辑：李大同）

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