sql智能分割字符函数

SQL code

FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))ASBEGINDECLARE @idx smallint,@value varchar(8000),@bcontinue bit,@iStrike smallint,@iDelimlength tinyintIF @sDelim = 'Space'BEGINSET @sDelim = ' 'ENDSET @idx = 0SET @sText = LTrim(RTrim(@sText))SET @iDelimlength = DATALENGTH(@sDelim)SET @bcontinue = 1IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))BEGINWHILE @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.  IF CHARINDEX(@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  SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))    END  ELSE  BEGIN--If you can抰 find the delimiter in the text, @sText is the last value [e-mail] @value = @sText    BEGIN    INSERT @retArray (idx, value)    VALUES (@idx, @value)    END  --Exit the WHILE loop.SET @bcontinue = 0  END  ENDENDELSEBEGINWHILE @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.  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  --One character remains.  --Insert the character, and exit the WHILE loop.  INSERT @retArray (idx, value)  VALUES (@idx, @sText)  SET @bcontinue = 0   ENDENDENDRETURNEND下面是调用示例：declare @no char(100)set @no='abc,cdesefd,efddc,mgns,aa'select * from fn_Split(@no,',')智能分割字符函数

把逗号去掉
能把些字分割为这样
 
嫦娥一号成功发射成功

分割为这样：
0 嫦
1 娥
2 一
3 号
4 成
5 功
6 发
7 射
8 成
9 功