Sql Server实现C#中Split(new char[]{',','+'...}相仿的SplitEx函数!!

来源：互联网发布：js中replaceall的用法编辑：程序博客网时间：2024/05/16 09:28

转自http://blog.csdn.net/ankoe/archive/2006/04/15/664386.aspx

一、分割字符串函数:Split
/*
*分割字符串函数
*Example:SELECT * FROM dbo.Split('p1+p2+p3','+')
*/
CREATE FUNCTION Split(
     @sText varchar(8000), --分割目标串
     @sDelim varchar(20) = ' ')--分割字符串
RETURNS
     @retArray TABLE( --返回分割后的串数据表
              idx smallint Primary Key, --主键
              value varchar(8000)--串值
      )
AS
BEGIN
DECLARE @idx smallint,
     @value varchar(8000),
     @bcontinue bit,
     @iStrike smallint,
     @iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

             SET @idx = 0
             SET @sText = LTrim(RTrim(@sText))
             SET @iDelimlength = DATALENGTH(@sDelim)
             SET @bcontinue = 1

             IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
             --如果分割串不为空时
BEGIN
WHILE @bcontinue = 1
  BEGIN
                          --如果在@sText是找到分割字符@sDelim的位置则将第一个元素插入表
  IF CHARINDEX(@sDelim, @sText)>0
   BEGIN
      SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
    BEGIN
      INSERT @retArray (idx, value) VALUES (@idx, @value)
    END
                                           --增加@idex值并取回下一个目标串
                                           SET @iStrike = DATALENGTH(@value) + @iDelimlength
            SET @idx = @idx + 1
      SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
   END
  ELSE
   BEGIN
                                        --如果在@sText里找不到@sDelim时，说明@sDelim已经不能再分割了，将@sText插入返回表@retArray
                                                     SET @value = @sText
    BEGIN
        INSERT @retArray (idx, value) VALUES (@idx, @value)
    END
                --设置退出循环标识
                                                     SET @bcontinue = 0
   END
  END
END
ELSE
BEGIN
WHILE @bcontinue=1
  BEGIN
                           --如果分割字符为空串时，将字符串中每个字符插入@retArray
  IF DATALENGTH(@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
   --插入字符并设置退出while标识
   INSERT @retArray (idx, value)
   VALUES (@idx, @sText)
   SET @bcontinue = 0
   END
END

END

RETURN
END

二、分割数字字符串函数:SplitINT
/*
*分割数字字符串函数
*Example:SELECT * FROM dbo.SplitINT('34+342+45','+')
*/
CREATE FUNCTION SplitINT(
     @sText varchar(8000), --分割目标串
     @sDelim varchar(20) = ' ')--分割字符串
RETURNS
     @retArray TABLE( --返回分割后的串数据表
              idx smallint Primary Key, --主键
              value INT--串值
      )
AS
BEGIN
DECLARE @idx smallint,
     @value varchar(8000),
     @bcontinue bit,
     @iStrike smallint,
     @iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

             SET @idx = 0
             SET @sText = LTrim(RTrim(@sText))
             SET @iDelimlength = DATALENGTH(@sDelim)
             SET @bcontinue = 1

END

RETURN
END

三、取得分割串在被分割串中最先出现分割字符的索引函数：SplitIndex
/*
**Function Name:SplitIndex
**Anthor: ankoe
**Create Time:2006-04-14
**Descript:本函数对@SplitStr分割串进行分析，并返回从左向右
** 最先出现分割字符的索引
*/
CREATE FUNCTION SplitIndex
(
@Str varchar(800),
@SplitStr varchar(30)
)
RETURNS int
AS
Begin

Declare @Result int
Declare @CurrentChar Char(1)
Declare @iLen int
Declare @Start int
Declare @cIndex int
Declare @strIndex varchar(100)--存储分隔符索引
Set @Result=0
Set @Start=1
Set @cIndex=0
Set @iLen=Len(@SplitStr)
DECLARE @I INT
SET @I=0
        While @iLen>0
        Begin
            Set @CurrentChar=SubString(@SplitStr,@Start,1)
            Set @iLen = @iLen-1
            Set @Start=@Start+2
            Set @cIndex=CharIndex(@CurrentChar,@Str)
              IF @I=0
               SET @strIndex=CAST(@cIndex as VARCHAR(100))
              ELSE
               SET @strIndex=@strIndex+','+CAST(@cIndex as VARCHAR(100))
            SET @I=@I+1
        End
        DECLARE @Count INT
        SET @Count=(SELECT COUNT(*) from dbo.SplitINT(@strIndex,',') where value<>'0')
        IF @Count>0
          SET @Result=(SELECT TOP 1 value from dbo.SplitINT(@strIndex,',') where value<>'0' order by value asc)
        ELSE
          Set @Result=0
     return @Result
End

四、扩展Split功能，支持类似C#中Split(new char[]{',','+'...}相仿的函数:SplitEx
/*
**Function Name:SplitEx
**Anthor:ankoe
**Create Time:2006-04-14
**Descript:本函数是对Split函数的扩展,Split函数只能以单串分隔字符串,
**              本函数提供了用分割串组分割字符串
*/
CREATE FUNCTION dbo.SplitEx
(
@RowData nvarchar(2000),
@SplitStr nvarchar(200)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
value nvarchar(100)
)
AS
BEGIN
        Declare @Var nvarchar(100)
        declare @endvar nvarchar(100)
        Set @Var=@RowData
        Declare @Index int
        Set @Index=dbo.SplitIndex(@Var,@SplitStr)
        WHILE @Index<>0
          BEGIN
            insert @RtnValue values(left(@Var,@Index-1))
            set @Var=SUBSTRING(@Var,@Index+1,len(@RowData)-@Index)
            Set @Index=dbo.SplitIndex(@Var,@SplitStr)
          END
            insert @RtnValue values(@Var)
            DELETE FROM @RtnValue WHERE value=''
Return
END

示例：
select * from dbo.SplitEx('p1+p2-P3','+,-')