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)

  

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