SQL Server 2012：如何拆分多分隔符字符串并解决截取报错？

看起来你在处理多分隔符字符串拆分和特定子串截取时遇到了麻烦，尤其是在SQL Server 2012这个没有STRING_SPLIT的版本里。我来给你分步解决这两个问题：

一、拆分含逗号、分号、空格的字符串 #

SQL Server 2012没有内置的多分隔符拆分函数，所以我们得自己写一个表值函数。思路是先把所有分隔符统一替换成逗号，再用递归CTE拆分：

CREATE FUNCTION dbo.SplitMultiDelimiterString (@InputString NVARCHAR(MAX), @Delimiters NVARCHAR(100))
RETURNS @OutputTable TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    -- 先把所有分隔符替换成逗号统一处理
    DECLARE @ReplacedString NVARCHAR(MAX) = @InputString
    DECLARE @Delimiter CHAR(1)
    DECLARE @DelimiterIndex INT = 1

    WHILE @DelimiterIndex <= LEN(@Delimiters)
    BEGIN
        SET @Delimiter = SUBSTRING(@Delimiters, @DelimiterIndex, 1)
        SET @ReplacedString = REPLACE(@ReplacedString, @Delimiter, ',')
        SET @DelimiterIndex = @DelimiterIndex + 1
    END

    -- 递归拆分处理后的字符串
    WITH SplitCTE AS (
        SELECT 
            CASE WHEN CHARINDEX(',', @ReplacedString) > 0 THEN LEFT(@ReplacedString, CHARINDEX(',', @ReplacedString) - 1)
                 ELSE @ReplacedString END AS Value,
            CASE WHEN CHARINDEX(',', @ReplacedString) > 0 THEN RIGHT(@ReplacedString, LEN(@ReplacedString) - CHARINDEX(',', @ReplacedString))
                 ELSE '' END AS RemainingString
        UNION ALL
        SELECT 
            CASE WHEN CHARINDEX(',', RemainingString) > 0 THEN LEFT(RemainingString, CHARINDEX(',', RemainingString) - 1)
                 ELSE RemainingString END AS Value,
            CASE WHEN CHARINDEX(',', RemainingString) > 0 THEN RIGHT(RemainingString, LEN(RemainingString) - CHARINDEX(',', RemainingString))
                 ELSE '' END AS RemainingString
        FROM SplitCTE
        WHERE RemainingString <> ''
    )
    INSERT INTO @OutputTable (Value)
    SELECT LTRIM(RTRIM(Value)) -- 去除字段前后空格
    FROM SplitCTE
    WHERE Value <> '' -- 过滤空值

    RETURN
END

使用示例（假设你的字段名为Content，表名为YourTable）：

SELECT * FROM dbo.SplitMultiDelimiterString((SELECT Content FROM YourTable WHERE Id = 1), ',; ')

二、修复'apples'后截取'Oranges'的报错 #

你遇到的报错几乎肯定是因为目标字符串里找不到apples，导致CHARINDEX返回0，后续的SUBSTRING之类的函数因为起始位置无效抛出错误。解决办法是先判断子串是否存在，再执行截取：

单字符串处理 #

DECLARE @Input NVARCHAR(MAX) = '你的原始多行字符串内容'
DECLARE @StartMarker NVARCHAR(50) = 'apples'
DECLARE @TargetSubstring NVARCHAR(50) = 'Oranges'

-- 先检查apples是否存在
IF CHARINDEX(@StartMarker, @Input) > 0
BEGIN
    -- 提取apples之后的所有内容
    DECLARE @AfterApples NVARCHAR(MAX) = SUBSTRING(@Input, CHARINDEX(@StartMarker, @Input) + LEN(@StartMarker), LEN(@Input))
    
    -- 检查目标子串Oranges是否存在
    IF CHARINDEX(@TargetSubstring, @AfterApples) > 0
    BEGIN
        -- 直接提取Oranges子串
        SELECT @TargetSubstring AS Result
        -- 如果需要提取apples和Oranges之间的内容，用下面的语句替换上面的SELECT
        -- SELECT SUBSTRING(@AfterApples, 1, CHARINDEX(@TargetSubstring, @AfterApples) - 1) AS Result
    END
    ELSE
    BEGIN
        SELECT '未找到''Oranges''子串' AS Result
    END
END
ELSE
BEGIN
    SELECT '未找到''apples''子串' AS Result
END

多行字段按行处理 #

如果你的字段是多行内容，先拆分每行再处理第一行：

-- 先创建拆分多行的函数
CREATE FUNCTION dbo.SplitStringByNewline (@InputString NVARCHAR(MAX))
RETURNS @OutputTable TABLE (LineNumber INT, LineContent NVARCHAR(MAX))
AS
BEGIN
    DECLARE @LineNumber INT = 1
    DECLARE @NewLinePos INT
    DECLARE @CurrentLine NVARCHAR(MAX)

    -- 处理Windows换行符（CR+LF）
    WHILE CHARINDEX(CHAR(13)+CHAR(10), @InputString) > 0
    BEGIN
        SET @NewLinePos = CHARINDEX(CHAR(13)+CHAR(10), @InputString)
        SET @CurrentLine = LEFT(@InputString, @NewLinePos - 1)
        INSERT INTO @OutputTable VALUES (@LineNumber, LTRIM(RTRIM(@CurrentLine)))
        SET @InputString = RIGHT(@InputString, LEN(@InputString) - @NewLinePos - 1)
        SET @LineNumber = @LineNumber + 1
    END

    -- 处理最后一行
    INSERT INTO @OutputTable VALUES (@LineNumber, LTRIM(RTRIM(@InputString)))
    RETURN
END

然后提取第一行并处理：

DECLARE @FirstLine NVARCHAR(MAX)
SELECT @FirstLine = LineContent FROM dbo.SplitStringByNewline((SELECT Content FROM YourTable WHERE Id = 1)) WHERE LineNumber = 1

-- 复用上面的子串截取逻辑处理@FirstLine
DECLARE @StartMarker NVARCHAR(50) = 'apples'
DECLARE @TargetSubstring NVARCHAR(50) = 'Oranges'

IF CHARINDEX(@StartMarker, @FirstLine) > 0
BEGIN
    DECLARE @AfterApples NVARCHAR(MAX) = SUBSTRING(@FirstLine, CHARINDEX(@StartMarker, @FirstLine) + LEN(@StartMarker), LEN(@FirstLine))
    
    IF CHARINDEX(@TargetSubstring, @AfterApples) > 0
    BEGIN
        SELECT @TargetSubstring AS Result
    END
    ELSE
    BEGIN
        SELECT '第一行中''apples''之后未找到''Oranges''子串' AS Result
    END
END
ELSE
BEGIN
    SELECT '第一行中未找到''apples''子串' AS Result
END

这样就能避免找不到子串时的报错，同时完成你的需求啦！

内容的提问来源于stack exchange，提问作者awilso11