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
Speak Your Mind