如何将SQL Server 2019字符串匹配代码改写适配SQL Server 2008 R2

前置操作：创建自定义字符串拆分函数 #

SQL Server 2008 R2 无内置STRING_SPLIT函数，需先执行以下代码创建自定义拆分函数：

CREATE FUNCTION dbo.fn_SplitString
(
    @InputStr VARCHAR(8000),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE (Value VARCHAR(1000))
AS
BEGIN
    DECLARE @Pos INT
    SET @Pos = CHARINDEX(@Delimiter, @InputStr)
    WHILE @Pos > 0
    BEGIN
        INSERT INTO @Result (Value)
        VALUES (LEFT(@InputStr, @Pos - 1))
        SET @InputStr = STUFF(@InputStr, 1, @Pos, '')
        SET @Pos = CHARINDEX(@Delimiter, @InputStr)
    END
    IF LEN(@InputStr) > 0
        INSERT INTO @Result (Value) VALUES (@InputStr)
    RETURN
END
GO

适配后完整业务代码 #

DECLARE 
    @s1 VARCHAR(100)='The Elf on the Shelf: A Christmas Musical. (Touring)',
    @s2 VARCHAR(100)='The Elf on the Shelf Musical, Baltimore',
    @totwords FLOAT,
    @s1words FLOAT;

WITH words AS (
    SELECT 1 s, 
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value,'!',''),'"',''),'*',''),'(',''),')',''),':',''),';',''),',',''),'.','') word
    FROM dbo.fn_SplitString(@s1,' ')
    UNION ALL
    SELECT 2 s, 
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value,'!',''),'"',''),'*',''),'(',''),')',''),':',''),';',''),',',''),'.','') word
    FROM dbo.fn_SplitString(@s2,' ')
),
-- 预计算总词数和s1的词数，兼容2008 R2窗口聚合限制
word_stats AS (
    SELECT 
        @totwords = COUNT(*) * 1.0,
        @s1words = SUM(CASE WHEN s=1 THEN 1 ELSE 0 END) * 1.0
    FROM words
),
matching AS (
    SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY word ORDER BY s) rn
    FROM words
),
final AS (
    SELECT *,
        COUNT(*) OVER(PARTITION BY word, s) repeating,
        CASE WHEN rn=2 AND rn=s THEN 1 ELSE 0 END p
    FROM matching
)
SELECT 
    (SUM(p) + MAX(CASE WHEN s=1 AND repeating>1 THEN repeating END))
    / MAX(CASE WHEN @totwords/@s1words>0.5 THEN @totwords-@s1words ELSE @s1words END) * 100 AS [Matching Words %]
FROM final

核心适配改动说明 #

• 用自定义fn_SplitString函数替代高版本内置的STRING_SPLIT函数，实现按空格拆分字符串
• 用多层嵌套REPLACE替代高版本的TRANSLATE函数，实现特殊字符批量清理
• 所有IIF逻辑替换为等价的CASE WHEN语句，兼容低版本语法
• 预计算总词数、s1词数变量，替代高版本支持的无ORDER BY聚合窗口写法
• 移除2008 R2支持不友好的OUTER APPLY (VALUES(...))写法，直接在计算列实现匹配标记逻辑

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