post

Sql Server中实现拆分字符串

Sql Server自定义函数,实现拆分字符串功能。返回结果为表。调用方法:SELECT * FROM dbo. SplitString(‘a,b,b’,’,’)

CREATE  FUNCTION [dbo].[SplitString]
(
     @string NVARCHAR(500),--被分的字符串
     @sp NVARCHAR(100) --分隔符
)

RETURNS
     @_strings TABLE
     (
          Id INT,
          String NVARCHAR(64),
          Inx INT,--便于查看结果规律
          Str1 NVARCHAR(65)
     )
AS
BEGIN
     DECLARE @count INT --计数
     SET @count=0
     DECLARE @index INT
     DECLARE @one NVARCHAR(64)--取下来的一节
     SET @index=Charindex(@sp,@string)
     WHILE(@index>0)
          BEGIN
               SET @one=left(@string,@index-1)
               SET @count=@count+1
               INSERT INTO @_strings (Id,String,Inx,Str1) VALUES(@count,@one,@index,@string)
               SET @string=right(@string,len(@string)-@index)
               SET @index=Charindex(@sp,@string)
          END
     INSERT INTO @_strings (Id,String,Inx,Str1) VALUES(@count+1,@string,@index,@one)
     RETURN
END
· 595 次浏览