split函数

--拆分字符串
CREATE FUNCTION [dbo].[fn_split](@str NVARCHAR(MAX), @split NVARCHAR(10)) RETURNS @table TABLE ([item] NVARCHAR(max)) AS BEGIN IF LEN(@split) = 0 BEGIN SET @split = N',' ENDDECLARE @xml XML; SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')INSERT INTO @table SELECT item FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item] FROM @xml.nodes('/x') t(c)) t WHERE item IS NOT NULLRETURN ENDdeclare @rn int ,@rn_count int,@n int,@n_count int,@messages varchar(500),@itemid int set @messages = 'Gold:0,Money:0,Annex1:385291156,Annex2:385291157,Annex3:385291158,Annex4:385291159,Annex5:385291160,GiftToken:0,Medal:0'select left(item,charindex(':',item)-1) xm, substring(item,charindex(':',item)+1,len(item)) value from dbo.fn_split(@messages,',') where item like 'Annex[0-9]%'

 

 

--split函数 SELECT * FROM dbo.split('581::579::519::279::406::361::560',':') ALTER Function [dbo].[Split](@Sql varchar(8000),@Splits varchar(10)) returns @temp Table (a varchar(100)) As Begin Declare @i Int Set @Sql = RTrim(LTrim(@Sql)) Set @i = CharIndex(@Splits,@Sql) While @i >= 1 Begin Insert @temp Values(Left(@Sql,@i-1)) Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i) Set @i = CharIndex(@Splits,@Sql) End If @Sql <> '' Insert @temp Values (@Sql) Return End

 

-- ============================================= -- Description: <拆分字符串函数> -- ============================================= Create FUNCTION [dbo].[Split] (@SplitString nvarchar(2000), -- 如果要传入NText类型，下面需要相应的修改，注释行为NText下同@Separator varchar(2) -- NVarChar(2) = N',' ) RETURNS @SplitStringsTable TABLE ([id] int identity(1,1),[value] varchar(8000) -- NVarChar(4000) ) AS BEGINDECLARE @CurrentIndex int;DECLARE @NextIndex int;DECLARE @ReturnText varchar(8000);-- NVarChar(4000)SELECT @CurrentIndex=1;WHILE(@CurrentIndex<=datalength(@SplitString)) -- DATALENGTH(@SplitString)/2BEGINSELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);IF(@NextIndex=0 OR @NextIndex IS NULL)SELECT @NextIndex=datalength(@SplitString)+1;--DATALENGTH(@SplitString)/2SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);INSERT INTO @SplitStringsTable([value])VALUES(@ReturnText);SELECT @CurrentIndex=@NextIndex+1;ENDRETURN; END

 

/*功能:获取字符串中的字母*/ CREATE FUNCTION dbo.F_Get_STR (@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGINWHILE PATINDEX('%[^a-z]%',@S)>0BEGINset @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')ENDRETURN @S END GO --测试 select dbo.F_Get_STR('测试ABC123ABC')

split实现获取第1到第2个分隔符中间的内容

alter function dbo.test_split(@str varchar(8000),@split varchar(100),@begin_pos int ,@end_pos int) returns varchar(8000) as begin
　　/*
　　　　create time :2019-04-13
　　　　author:chaoqun.guo
　　*/declare @count int,@str1 varchar(8000),@i intset @i=charindex(@split,@str)set @count=0SET @STR1=''while @i>0 beginif @count>=@begin_pos and @count< @end_posbeginIF @STR1=''BEGINSET @STR1=LEFT(@str,@i-1)ENDelsebeginSET @STR1=@str1+@split+ LEFT(@str,@i-1)endendset @str=substring(@str,@i+1,len(@str)-@i)set @i=charindex(@split,@str)set @count=@count+1endIF @STR<>''BEGINif @count>=@begin_pos and @count< @end_posbeginIF @STR1=''BEGINSET @STR1=@strENDelsebeginSET @STR1=@str1+@split+@strendendENDreturn @str1 end go select dbo.test_split('123,234,456,678',',',0,5)

 

  

 

 

 

SQL SERVER 2016的新函数STRING_SPLIT可以直接按指定分隔符拆分字符串；

--sql server 2016 SELECT * FROM STRING_SPLIT('abc,ab,c', ',')

SQL SERVER 2017的新函数STRING_AGG可以直接按指定分隔符合并字符串；

--sql server 2017 SELECT STRING_AGG(name, ',') from sys.objects

 

转载于:https://www.cnblogs.com/gered/p/9887536.html

总结

以上是生活随笔为你收集整理的split函数的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。