SQLSERVER表值函数--将字符串按分隔符拆分成表

SQLSERVER表值函数--将字符串按分隔符拆分成表

输入：

• 字符串 = 'DADA,RERDE,FRED'

输出：

• 表：

  

实现函数代码：

-- 已下两种函数可任选一个
-- 函数写法1：
CREATE FUNCTION [dbo].[SplitTextToTable]
(
  @SplitString varchar(8000),
  @Separator varchar(10) = ','
)
RETURNS @SplitStringsTable TABLE
(
  [VALUE] varchar(1000)
)
AS
BEGIN
     DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     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;
     END
     RETURN;
END
GO

--函数写法2：
CREATE FUNCTION dbo.SplitStringToTable
(
    @SplitString varchar(8000),
    @Separator varchar(10) = ',' 
)
RETURNS @SplitStringsTable TABLE
(
  [VALUE] varchar(1000)
)
AS
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
    END 
    INSERT INTO @SplitStringsTable([VALUE])
    SELECT SUBSTRING(@SplitString,@CurrentIndex, LEN(@SplitString)-@CurrentIndex+1)
    RETURN 
END 
GO

--验证：两个函数输出应一样
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, ',')
) A 
-- 方法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)
)
AS
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
    END 
    INSERT INTO @SplitStringsTable([VALUE])
    SELECT SUBSTRING(@SplitString,@CurrentIndex, LEN(@SplitString)-@CurrentIndex+1)
    RETURN 
END 
GO