创建函数：
CREATE FUNCTION[dbo].[split](@str NVARCHAR(max),@spliter NVARCHAR(10))
--@str:目标字符串
--@spliter:分隔符
RETURNS @tb TABLE(ch NVARCHAR(max))
AS
BEGIN
DECLARE @Num INT,@Pos INT, @NextPos INT
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@str))
BEGIN
SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@str) + 1
INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))))
SELECT @Pos = @NextPos+1
END
RETURN
END
GO
使用方式：
SELECT * FROM dbo.split('1,2,3,4',',') --注：多行单列的数据表
测试语句：
IF object_id(N'[dbo].[split]') IS NOT NULL --检查函数【[dbo].[split]】是否存在
BEGIN
DROP FUNCTION [dbo].[split] --删除函数
END
GO CREATE FUNCTION[dbo].[split](@str NVARCHAR(max),@spliter NVARCHAR(10))
--@str:目标字符串
--@spliter:分隔符
RETURNS @tb TABLE(ch NVARCHAR(max))
AS
BEGIN
DECLARE @Num INT,@Pos INT, @NextPos INT
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@str))
BEGIN
SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@str) + 1
INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))))
SELECT @Pos = @NextPos+1
END
RETURN
END
GO SELECT * FROM dbo.split('1,2,3,4',',') DROP FUNCTION [dbo].[split]
GO
说明：
1、仅支持文本字符串分隔！！！