Sql Server Split函数

时间：2011-05-19 来源：colder

创建表值函数Split

Create FUNCTION [dbo].[split]

( @Long_str VARCHAR(8000) , @split_str VARCHAR(100) ) RETURNS @tmp TABLE ( ID INT IDENTITY PRIMARY KEY , short_str VARCHAR(8000) ) AS BEGIN DECLARE @long_str_Tmp VARCHAR(8000) , @short_str VARCHAR(8000) , @split_str_length INT SET @split_str_length = LEN(@split_str) IF CHARINDEX(@split_str, @Long_str) <= 0 BEGIN INSERT INTO @tmp SELECT @Long_str END ELSE BEGIN IF CHARINDEX(@split_str, @Long_str) = 1 SET @long_str_Tmp = SUBSTRING(@Long_str, @split_str_length + 1, LEN(@Long_str) - @split_str_length) ELSE SET @long_str_Tmp = @Long_str IF CHARINDEX(REVERSE(@split_str), REVERSE(@long_str_Tmp)) > 1 SET @long_str_Tmp = @long_str_Tmp + @split_str ELSE SET @long_str_Tmp = @long_str_Tmp WHILE CHARINDEX(@split_str, @long_str_Tmp) > 0 BEGIN SET @short_str = SUBSTRING(@long_str_Tmp, 1, CHARINDEX(@split_str, @long_str_Tmp) - 1) DECLARE @long_str_Tmp_LEN INT , @split_str_Position_END INT SET @long_str_Tmp_LEN = LEN(@long_str_Tmp) SET @split_str_Position_END = LEN(@short_str) + @split_str_length SET @long_str_Tmp = REVERSE(SUBSTRING(REVERSE(@long_str_Tmp), 1, @long_str_Tmp_LEN - @split_str_Position_END)) IF @short_str <> '' INSERT INTO @tmp SELECT @short_str END END RETURN END

用游标循环获取Split的值

DECLARE cur_user CURSOR FOR SELECT short_str FROM dbo.split(@UserIDs,',') OPEN cur_user FETCH NEXT FROM cur_user INTO @userId WHILE @@fetch_status = 0 BEGIN INSERT INTO #User SELECT UserId , MO_StaffID , UserName FROM dbo.aspnet_Users WHERE UserId = @userId ; FETCH NEXT FROM cur_user INTO @userId END CLOSE cur_user

DEALLOCATE cur_user