SQL中实现SPLIT函数几种方法总结(必看篇)_Mysql

SQL中实现SPLIT函数几种方法总结(必看篇)

2020-06-22 16:02jingxian Mysql

下面小编就为大家带来一篇SQL中实现SPLIT函数几种方法总结(必看篇)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧

例1

代码如下

				?

									create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))

									returns @temp table(a varchar(100))

									--实现split功能 的函数

									--date :2003-10-14

									as

									begin

									declare @i int

									set @SourceSql=rtrim(ltrim(@SourceSql))

									set @i=charindex(@StrSeprate,@SourceSql)

									while @i>=1

									begin

									insert @temp values(left(@SourceSql,@i-1))

									set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)

									set @i=charindex(@StrSeprate,@SourceSql)

									end

									if @SourceSql<>''

									insert @temp values(@SourceSql)

									return

									end

									select * from dbo.f_split('1,2,3,4',',')

									a 

									-------------------- 

									1

									2

									3

									4

									（所影响的行数为 4 行）

例2

代码如下

				?

									--SQL Server Split函数

									--Author:zc_0101 

									--说明：

									--支持分割符多字节

									--使用方法 

									--Select * FROM DBO.F_SQLSERVER_SPLIT('1203401230105045','0') 

									--select * from DBO.F_SQLSERVER_SPLIT('abc1234a12348991234','1234') 

									--Select * from DBO.F_SQLSERVER_SPLIT('ABC',',') 

									CREATE FUNCTION F_SQLSERVER_SPLIT(@Long_str varchar(8000),@split_str varchar(100)) 

									RETURNS @tmp TABLE( 

									ID inT IDENTITY PRIMARY KEY, 

									short_str varchar(8000) 

									) 

									AS

									BEGIN

									DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int

									SET @split_str_length = LEN(@split_str) 

									IF CHARINDEX(@split_str,@Long_str)=1 

									SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)

									ELSE

									SET @long_str_Tmp=@Long_str

									IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1 

									SET @long_str_Tmp=@long_str_Tmp+@split_str 

									ELSE

									SET @long_str_Tmp=@long_str_Tmp 

									IF CHARINDEX(@split_str,@long_str_Tmp)=0

									Insert INTO @tmp select @long_str_Tmp 

									ELSE

									BEGIN

									WHILE CHARINDEX(@spl(www.zzvips.com)it_str,@long_str_Tmp)>0 

									BEGIN

									SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1) 

									DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int

									SET @long_str_Tmp_LEN = LEN(@long_str_Tmp) 

									SET @split_str_Position_END = LEN(@short_str)+@split_str_length 

									SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))

									IF @short_str<>'' Insert INTO @tmp select @short_str 

									END

									END

									RETURN

									END

例3

Sql2000andSql2005实用的Split函数

代码如下

				?

									sql2000 

									CREATE FUNCTION [dbo].[splitstring_array] 

									( 

									@string nvarchar(4000),@split char(1) 

									) 

									RETURNS @array table

									( 

									oneStr nvarchar(100) 

									) 

									AS

									BEGIN

									declare @v_code varchar(60) 

									--zell 2006-05-26 

									--set @string = replace(@string,' ',@split) 

									--set @string = replace(@string,'，',@split) 

									while len(@string) > 0 

									begin

									if charindex(@split,@string,1) != 0 

									begin

									set @v_code = substring(@string,1,charindex(@split,@string,1)-1) 

									set @string = substring(@string,charindex(@split,@string,1)+1,len(@string)) 

									end

									else if charindex(@split,@string,1) = 0 

									begin

									set @v_code = @string 

									set @string = ''

									end

									insert into @array(onestr) values(@v_code) 

									end

									RETURN

									END

									sql2005 

									CREATE function [dbo].[func_splitid] 

									(@str varchar(max),@split varchar(10)) 

									RETURNS @t Table (c1 nvarchar(100)) 

									AS

									BEGIN

									DECLARE @x XML 

									SET @x = CONVERT(XML,'<items><item id="' + REPLACE(@str, @split, '"/><item id="') + '"/></items>') 

									INSERT INTO @t SELECT x.item.value('@id[1]', 'nvarchar(100)') FROM @x.nodes('//items/item') AS x(item) 

									RETURN

									END