在MSSQL的创建Split函数

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =========================================================
-- Create date: 2008-9-2
-- Author: jommy
-- Description:	将字符串分割,返回表变量
-- Example: SELECT * FROM [dbo].[Split]('1,2,3,4,5',',')
-- =========================================================
CREATE FUNCTION [dbo].[Split](@SourceString varchar(4000),@SeparatChar varchar(10)=',')
RETURNS @temp TABLE(KeyValue varchar(100))
AS
BEGIN
	DECLARE @i int
	SET @SourceString=Rtrim(Ltrim(@SourceString))
	SET @i=Charindex(@SeparatChar,@SourceString)
	WHILE @i>=1
		BEGIN
			INSERT @temp VALUES (Left(@SourceString,@i-1))
			SET @SourceString=Substring(@SourceString,@i+1,Len(@SourceString)-@i)
			SET @i=Charindex(@SeparatChar,@SourceString)
		END
	IF @SourceString<>'\'
		INSERT @temp VALUES (@SourceString)
	RETURN
END
GO