SQL 分割字符串

很早以前写过的几个小方法，算是留作纪念 

--通过分隔符将字符串进行分割，获取指定索引的值
create function [dbo].[f_GetStringByIndex](@concect varchar(300),@split varchar(2),@index int)
returns varchar(100)
as    
begin    
        declare @current int =1    --当前循环数
        declare @start int = 1--开始坐标
        declare @location int    
        set @location=charindex(@split,@concect)
        while(@location<>0 and @index>@current )
            begin
                set @start=@location+1
                set @location=charindex(@split,@concect,@start)
                set @current=@current+1
            end
    
    if(@location=0)set @location=LEN(@concect)+1

return substring(@concect,@start,@location-@start)
end

--通过分隔符将字符串分割 获取分割后字符串集合长度
create function [dbo].[f_GetArrayLenth](@str varchar(500),@split varchar(2))
returns int
as
    begin
        declare @count int
        declare @location int
        declare @start int
        
        set @location=charindex(@split,@str)
        set @str=rtrim(ltrim(@str))
        set @count=1
        while(@location<>0)
            begin
            
                set @count=@count+1
                set @start=@location+1
                set @location=charindex(@split,@str,@start)
            end
    
            return @count
    end

--通过分隔符将字符串分割，获取字符串集合
CREATE  FUNCTION [dbo].[FN_SPLITSTRING](@STR VARCHAR(1000),@SPLIT VARCHAR(2))
RETURNS @T1 TABLE(col VARCHAR(100))
AS
   BEGIN
           WHILE(CHARINDEX(@SPLIT,@STR)<>0)
             BEGIN
             DECLARE @VALUE VARCHAR(100)
             SET @VALUE=SUBSTRING(@STR,0,CHARINDEX(@SPLIT,@STR))
             INSERT INTO @T1 VALUES(@VALUE)

             SELECT @STR=STUFF(@STR,1,LEN(@VALUE)+1,'')
             
             END
        
       IF LEN(RTRIM(LTRIM(@STR)))>0
           INSERT INTO @T1 VALUES(@STR)
          RETURN
   END