--方法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
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
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]
GO
--方法3:使用永久性分拆辅助表法
--字符串分拆辅助表
SELECT top 8000 ID=IDENTITY(int,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO
--字符串分拆处理函数
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 )
总结以上是内存溢出为你收集整理的sqlserver字符串拆分(split)方法汇总全部内容,希望文章能够帮你解决sqlserver字符串拆分(split)方法汇总所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)