SQL自定义函数split
Create Function Split( @Strs As Nvarchar ( 1024 ), @Separator as Nvarchar ( 10 ), @Index as Int ) Returns Nvarchar ( 1024 ) As begin Declare @i As Int , @charpos As Nvarchar ( 1024 ) Set @charpos = @Strs Set @i = 1 If @Index < 0 Begin Set @charpos = ' 超出下界 ' End Else Begin While @i <= ( @Index - 1 ) Begin If CharIndex ( @Separator , @charpos ) > 0 Begin Set @charpos = Substring ( @charpos , CharIndex ( @Separator , @charpos ) + 1 , Len ( @charpos ) - CharIndex ( @Separator , @charpos )) End Else Begin Set @charpos = ' 超出上界 ' Break End Set @i = @i + 1 End If @charpos <> ' 超出上界 ' Begin If CharIndex ( @Separator , @charpos ) > 0 Begin Set @charpos = Left ( @charpos , CharIndex ( @Separator , @charpos ) - 1 ) End End End Return @charpos End -- 调用 select dbo.Split( ' sdf|abc|csc|aldsfj|sfj|取出原素|asdf|adf|... ' , ' | ' , 6 ) -- 返回 取出原素将数组(分隔字符串)返回阵列（表）
-- 将数组(分隔字符串)返回阵列（表） -- drop function fn_Split -- 自定义函数 CREATE FUNCTION fn_Split( @sText nvarchar ( 4000 ), @sDelim varchar ( 20 ) = ' ' ) RETURNS @retArray TABLE (idx smallint Primary Key , value varchar ( 8000 )) AS BEGIN DECLARE @idx smallint , @value nvarchar ( 4000 ), @bcontinue bit , @iStrike smallint , @iDelimlength tinyint IF @sDelim = ' Space ' BEGIN SET @sDelim = ' ' END SET @idx = 0 SET @sText = LTrim ( RTrim ( @sText )) SET @iDelimlength = DATALENGTH ( @sDelim ) SET @bcontinue = 1 IF NOT (( @iDelimlength = 0 ) or ( @sDelim = ' Empty ' )) BEGIN WHILE @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 if @idx = 12 begin set @bcontinue = 0 end SET @sText = LTrim ( right ( @sText ,( DATALENGTH ( @sText ) - @iStrike ) / 2 )) END ELSE BEGIN -- If you can't find the delimiter in the text, @sText is the last value in -- @retArray. SET @value = @sText BEGIN INSERT @retArray (idx, value) VALUES ( @idx , @value ) END -- Exit the WHILE loop. SET @bcontinue = 0 END END END ELSE BEGIN WHILE @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 END END END RETURN END -- 测试 declare @no char ( 100 ) set @no = ' china 中国,%…-- desefd,e 中国人fddc,mgns,a a ' select * from fn_Split( @no , ' , ' )--返回结果idx [value]0 china 中国1 %…-- desefd2 e 中国人fddc3 mgns4 a a
上面的判断太多了不易于理解，下面转了一个简单的意思基本相同代码简单很多
-- ============================================= -- Author: 阿瑞 -- Create date: 2008-03-19 -- Description: split函数 -- Debug:select * from dbo.Fun_Split('ABC:BC:C:D:E',':') -- ============================================= CREATE FUNCTION [ dbo ] . [ Fun_Split ] ( @SourceSql varchar ( 8000 ), @StrSeprate varchar ( 10 )) RETURNS @TEMP_Table TABLE (a varchar ( 100 )) AS BEGIN DECLARE @i int SET @SourceSql = rtrim ( ltrim ( @SourceSql )) SET @i = charindex ( @StrSeprate , @SourceSql ) WHILE @i >= 1 BEGIN INSERT @TEMP_Table VALUES ( left ( @SourceSql , @i - 1 )) SET @SourceSql = substring ( @SourceSql , @i + 1 , len ( @SourceSql ) - @i ) SET @i = charindex ( @StrSeprate , @SourceSql ) END if @SourceSql <> ' / ' INSERT @TEMP_Table values ( @SourceSql ) RETURN END
调用：select * from dbo.Fun_Split('ABC:BC:C:D:E',':')
结果：
