第一种方式普通循环取值
CREATE FUNCTION [dbo].[f_split] ( @string VARCHAR(MAX) , @separator CHAR ) RETURNS @return TABLE ( value VARCHAR(200) ) AS BEGIN DECLARE @len INT; SET @len = LEN(@string); IF ( SUBSTRING(@string, @len, 1) <> @separator ) SET @string = @string + @separator; DECLARE @strtemp VARCHAR(200); DECLARE @index1 INT; DECLARE @index2 INT; SET @index1 = 0; SET @index2 = CHARINDEX(@separator, @string, 1); WHILE @index2 <> 0 BEGIN SET @strtemp = SUBSTRING(@string, @index1 + 1, @index2 - @index1 - 1); INSERT @return ( [value] ) VALUES ( @strtemp ); SET @index1 = @index2; SET @index2 = CHARINDEX(@separator, @string, @index1 + 1); END; RETURN; END;
第二种方式借助中间表
1 CREATE FUNCTION [dbo].[f_split] 2 ( 3 @string VARCHAR(MAX) , 4 @separator CHAR 5 ) 6 RETURNS @return TABLE ( value VARCHAR(200) ) 7 AS 8 BEGIN 9 INSERT INTO @return 10 SELECT SUBSTRING(@string, b.number, 11 CHARINDEX(@separator, @string + @separator, 12 b.number) - b.number) 13 FROM master..spt_values b 14 WHERE CHARINDEX(@separator, @separator + @string, b.number) = b.number 15 AND b.type = 'P'; 16 RETURN; 17 END;