SQL自定义函数split 将数组(分隔字符串)返回阵列（表）

SQL自定义函数split

CreateFunction Split(@StrsAsNvarchar(1024),@SeparatorasNvarchar(10),@IndexasInt)
ReturnsNvarchar(1024) As
begin
     Declare@iAsInt, @charposAsNvarchar(1024)
     Set@charpos=@Strs
     Set@i=1
     If@Index<0
            Begin
              Set@charpos='超出下界'  
            End
     Else
       Begin 
       While@i<= (@Index-1)
            Begin
             IfCharIndex(@Separator, @charpos) >0
              Begin
                 Set@charpos=Substring(@charpos, CharIndex(@Separator, @charpos) +1, Len(@charpos) -CharIndex(@Separator, @charpos))
              End
       Else
       Begin
         Set@charpos='超出上界'
         Break
       End
      Set@i=@i+1
    End  

   If@charpos<>'超出上界'
        Begin
         IfCharIndex(@Separator, @charpos) >0 
             Begin
                   Set@charpos=Left(@charpos, CharIndex(@Separator, @charpos) -1)
              End
        End
      End
     Return@charpos
End
--调用
select dbo.Split('sdf|abc|csc|aldsfj|sfj|取出原素|asdf|adf|...','|',6)
--返回 取出原素

将数组(分隔字符串)返回阵列（表）

--将数组(分隔字符串)返回阵列（表）
--drop function fn_Split
--自定义函数
CREATE  FUNCTION fn_Split(@sTextnvarchar(4000), @sDelimvarchar(20) ='')
RETURNS@retArrayTABLE (idx smallintPrimaryKey, value varchar(8000))
AS
    BEGIN
        DECLARE@idxsmallint,
        @valuenvarchar(4000),
        @bcontinuebit,
        @iStrikesmallint,
        @iDelimlengthtinyint
        IF@sDelim='Space'
            BEGIN
                SET@sDelim=''
            END
        SET@idx=0
        SET@sText=LTrim(RTrim(@sText))
        SET@iDelimlength=DATALENGTH(@sDelim)
        SET@bcontinue=1
        IFNOT ((@iDelimlength=0) or (@sDelim='Empty'))
            BEGIN
                WHILE@bcontinue=1
                  BEGIN
                --If you can find the delimiter in the text, retrieve the first element and
                --insert it with its index into the return table.

                      IFCHARINDEX(@sDelim, @sText)>0
                         BEGIN
                             SET@value=SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
                                BEGIN
                                    INSERT@retArray (idx, value) VALUES (@idx, @value)
                                END
                         
                        --Trim the element and its delimiter from the front of the string.
                          --Increment the index and loop.
                            SET@iStrike=DATALENGTH(@value) +@iDelimlength
                            SET@idx=@idx+1
                            if@idx=12
                                begin
                                   set@bcontinue=0
                                end
                            SET@sText=LTrim(right(@sText,(DATALENGTH(@sText) -@iStrike)/2))                                               
                         END                           
                      ELSE
                          BEGIN
                        --If you can't find the delimiter in the text, @sText is the last value in
                        --@retArray.
                            SET@value=@sText
                                BEGIN
                                    INSERT@retArray (idx, value)
                                    VALUES (@idx, @value)
                                END
                          --Exit the WHILE loop.
                            SET@bcontinue=0
                          END
                  END
            END
        ELSE
            BEGIN
                WHILE@bcontinue=1
                    BEGIN
                  --If the delimiter is an empty string, check for remaining text
                  --instead of a delimiter. Insert the first character into the
                  --retArray table. Trim the character from the front of the string.
                --Increment the index and loop.
                      IFDATALENGTH(@sText)>1
                          BEGIN
                            SET@value=SUBSTRING(@sText,1,1)
                                BEGIN
                                    INSERT@retArray (idx, value) VALUES (@idx, @value)
                                END
                             SET@idx=@idx+1
                             SET@sText=SUBSTRING(@sText,2,DATALENGTH(@sText)-1)     
                          END
                      ELSE
                          BEGIN
                          --One character remains.
                          --Insert the character, and exit the WHILE loop.
                             INSERT@retArray (idx, value) VALUES (@idx, @sText)
                             SET@bcontinue=0
                          END
                    END
            END
      RETURN
    END
--测试
declare@nochar(100)
set@no='china 中国,%…-- desefd,e 中国人fddc,mgns,a a'
select*from fn_Split(@no,',')

面的判断太多了不易于理解，下面转了一个简单的意思基本相同代码简单很多

-- ============================================='
-- =============================================
CREATE FUNCTION[dbo].[Fun_Split]
(
@SourceSqlvarchar(8000),
@StrSepratevarchar(10)
)
RETURNS
@TEMP_TableTABLE (a varchar(100))
AS
BEGIN
  DECLARE@iint
  SET@SourceSql=rtrim(ltrim(@SourceSql))
  SET@i=charindex(@StrSeprate,@SourceSql)
  WHILE@i>=1
    BEGIN
     INSERT@TEMP_TableVALUES(left(@SourceSql,@i-1))
     SET@SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
     SET@i=charindex(@StrSeprate,@SourceSql)
  END
if@SourceSql<>'/'
   INSERT@TEMP_Tablevalues(@SourceSql)
RETURN
END