字符串分拆函数:

/**//****** 对象: UserDefinedFunction [dbo].[Split] 脚本日期: 04/23/2007 16:02:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Librading
-- Create date:
-- Description: 字符串分拆函数
-- =============================================
CREATE FUNCTION [dbo].[Split]
(
@Input varchar(4000), -- 输入字符串
@Separator varchar(4000) = ',' -- 分隔符
)
RETURNS
@Result TABLE
(
Value varchar(4000)
)
AS
BEGIN

DECLARE @InputLength int;
SET @InputLength = LEN(@Input);
DECLARE @SeparatorLength int;
SET @SeparatorLength = LEN(@Separator);
DECLARE @LastIndex int;
SET @LastIndex = 0;
DECLARE @CurrentIndex int;
SET @CurrentIndex = -1;

WHILE @CurrentIndex != 0
BEGIN
SET @CurrentIndex = CHARINDEX(@Separator, @Input, @LastIndex );

IF @CurrentIndex != 0
INSERT @Result
(
Value
)
VALUES
(
SUBSTRING(@Input, @LastIndex, @CurrentIndex - @LastIndex)
)
ELSE
INSERT @Result
(
Value
)
VALUES
(
SUBSTRING(@Input, @LastIndex, @InputLength + 1 - @LastIndex)
)

SET @LastIndex = @CurrentIndex + @SeparatorLength;
END

RETURN

END

使用例子:
1、简单的例子:
select * from dbo.Split('a,b,c', ',');
2、拆分后的表作为 int 或者其他数据类型而不是字符串:
DECLARE @Table TABLE
(
ID int
);

INSERT @Table SELECT * FROM [dbo].[Split]('1---2---3---4---5', '---');

SELECT * FROM @Table;