SQL拆分函數

CREATE FUNCTION [DBO].[F_CHAR_SPLIT_TO_TABLE](@CHAR_LIST VARCHAR(MAX),@SIGN NVARCHAR(1))RETURNS @T_1 TABLE(ID INT,VALUE VARCHAR(MAX))AS  BEGIN DECLARE @INTPOS INT,@SP_CHAR VARCHAR(MAX)DECLARE @INDEX INTSET @INDEX=1IF LEN(ISNULL(@CHAR_LIST,''))=0RETURNELSE IF LEN(ISNULL(@SIGN,''))=0INSERT INTO @T_1 SELECT @INDEX,@CHAR_LISTELSEBEGINSET @INTPOS=CHARINDEX(@SIGN,@CHAR_LIST)WHILE (@INTPOS>=0)BEGINIF @INTPOS>0 SET @SP_CHAR=LEFT(@CHAR_LIST,@INTPOS-1)ELSESET @SP_CHAR=@CHAR_LISTINSERT INTO @T_1 SELECT @INDEX,@SP_CHARSET @CHAR_LIST=RIGHT(@CHAR_LIST,LEN(@CHAR_LIST)-@INTPOS)IF @INTPOS>0SET @INTPOS=CHARINDEX(@SIGN,@CHAR_LIST)ELSESET @INTPOS=-1SET @INDEX=@INDEX+1ENDENDRETURNEND