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',' END DECLARE @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 NULL RETURN END declare @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 BEGIN DECLARE @CurrentIndex int; DECLARE @NextIndex int; DECLARE @ReturnText varchar(8000);-- NVarChar(4000) SELECT @CurrentIndex=1; WHILE(@CurrentIndex<=datalength(@SplitString)) -- DATALENGTH(@SplitString)/2 BEGIN SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); IF(@NextIndex=0 OR @NextIndex IS NULL) SELECT @NextIndex=datalength(@SplitString)+1;--DATALENGTH(@SplitString)/2 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText); SELECT @CurrentIndex=@NextIndex+1; END RETURN; END
/*功能:获取字符串中的字母*/ CREATE FUNCTION dbo.F_Get_STR (@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^a-z]%',@S)>0 BEGIN set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'') END RETURN @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 int set @i=charindex(@split,@str) set @count=0 SET @STR1='' while @i>0 begin if @count>=@begin_pos and @count< @end_pos begin IF @STR1='' BEGIN SET @STR1=LEFT(@str,@i-1) END else begin SET @STR1=@str1+@split+ LEFT(@str,@i-1) end end set @str=substring(@str,@i+1,len(@str)-@i) set @i=charindex(@split,@str) set @count=@count+1 end IF @STR<>'' BEGIN if @count>=@begin_pos and @count< @end_pos begin IF @STR1='' BEGIN SET @STR1=@str END else begin SET @STR1=@str1+@split+@str end end END return @str1 end go select dbo.test_split('123,234,456,678',',',0,5)
![](https://img2018.cnblogs.com/blog/1302413/201904/1302413-20190411163401129-1427092194.png)
![](https://img2018.cnblogs.com/blog/1302413/201904/1302413-20190411163417674-686940618.png)
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