DECLARE @ReturnText varchar(8000);
SELECT @CurrentIndex=1;
WHILE @CurrentIndex<=len(@SplitString)
BEGIN
SET @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF @NextIndex=0 OR @NextIndex IS NULL
SET @NextIndex=len(@SplitString)+1;
SET @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([VALUE]) VALUES(@ReturnText);
SET @CurrentIndex=@NextIndex+1;
RETURN;
--函数写法2：
CREATE FUNCTION dbo.SplitStringToTable
@SplitString varchar(8000),
@Separator varchar(10) = ','
RETURNS @SplitStringsTable TABLE
[VALUE] varchar(1000)
BEGIN
DECLARE @CurrentIndex INT=1
DECLARE @SeparatorIndex INT =1
SET @SeparatorIndex = CHARINDEX(@Separator, @SplitString, @CurrentIndex)
WHILE @SeparatorIndex>0
BEGIN
INSERT INTO @SplitStringsTable([VALUE])
SELECT SUBSTRING(@SplitString,@CurrentIndex, @SeparatorIndex-@CurrentIndex)
SET @CurrentIndex = @SeparatorIndex+1
SET @SeparatorIndex = CHARINDEX(@Separator, @SplitString, @CurrentIndex)
--SELECT @SeparatorIndex, @CurrentIndex
INSERT INTO @SplitStringsTable([VALUE])
SELECT SUBSTRING(@SplitString,@CurrentIndex, LEN(@SplitString)-@CurrentIndex+1)
RETURN
--验证：两个函数输出应一样
DECLARE @SplitString VARCHAR(100)= 'DADA,RERDE,FRED'
SELECT * FROM dbo.SplitTextToTable(@SplitString,',')
SELECT * FROM dbo.SplitStringToTable(@SplitString, ',')
有时对输出表进行处理时，需要用到字符串的出现顺序，这种情况下有必要给输出表加一个序号列（见下图），有两种解决方案：
-- 方法1：使用ROW_NUMBER()函数
SELECT ROW_NUMBER() OVER(ORDER BY AA) RN, A.[VALUE]
FROM (
-- 因为ROW_NUMBER()必须要依据一个字段来排序，因此增加一个0作为排序字段
SELECT 0 AS AA,* FROM dbo.SplitTextToTable(@SplitString, ',')
-- 方法2：修改函数，输出中加SNO列（修改SplitStringToTable或SplitTextToTable都可以）
ALTER FUNCTION dbo.SplitStringToTable
@SplitString varchar(8000),
@Separator varchar(10) = ','
RETURNS @SplitStringsTable TABLE
[SNO] INT IDENTITY(1,1), --顺序号
[VALUE] varchar(1000)
BEGIN
DECLARE @CurrentIndex INT=1
DECLARE @SeparatorIndex INT =1
SET @SeparatorIndex = CHARINDEX(@Separator, @SplitString, @CurrentIndex)
WHILE @SeparatorIndex>0
BEGIN
INSERT INTO @SplitStringsTable([VALUE])
SELECT SUBSTRING(@SplitString,@CurrentIndex, @SeparatorIndex-@CurrentIndex)
SET @CurrentIndex = @SeparatorIndex+1
SET @SeparatorIndex = CHARINDEX(@Separator, @SplitString, @CurrentIndex)
--SELECT @SeparatorIndex, @CurrentIndex
INSERT INTO @SplitStringsTable([VALUE])
SELECT SUBSTRING(@SplitString,@CurrentIndex, LEN(@SplitString)-@CurrentIndex+1)
RETURN
