多列多匹配词的区分大小写LIKE比较语法简化咨询

以下几种方法可以避免重复编写排序规则串，同时简化多列多匹配词的LIKE语句：

方法1：使用CROSS APPLY统一处理列的排序规则 #

将需要查询的列转换为指定大小写敏感的排序规则后，统一进行模式匹配，同时可将匹配模式也整理为集合，进一步简化语句：

SELECT f.*
FROM foobar f
CROSS APPLY (
    -- 一次性为所有需要查询的列指定排序规则
    VALUES
        (f.col1 COLLATE SQL_Latin1_General_CP1_CS_AS),
        (f.col2 COLLATE SQL_Latin1_General_CP1_CS_AS),
        (f.col3 COLLATE SQL_Latin1_General_CP1_CS_AS)
) AS cols(val)
CROSS JOIN (
    -- 所有匹配模式集中定义
    VALUES
        ('%needle1%'),
        ('needle2%'),
        ('%nee%dle3%')
) AS patterns(pat)
WHERE cols.val LIKE patterns.pat
GROUP BY f.* -- 分组去重，避免同一行因多列/多模式匹配出现重复结果

方法2：用CTE预先格式化列 #

通过CTE为需要查询的列创建大小写敏感的别名，后续查询直接使用别名即可：

WITH CaseSensitiveCols AS (
    SELECT 
        *,
        col1 COLLATE SQL_Latin1_General_CP1_CS_AS AS col1_cs,
        col2 COLLATE SQL_Latin1_General_CP1_CS_AS AS col2_cs,
        col3 COLLATE SQL_Latin1_General_CP1_CS_AS AS col3_cs
    FROM foobar
)
SELECT *
FROM CaseSensitiveCols
WHERE col1_cs LIKE '%needle1%'
   OR col2_cs LIKE '%needle1%'
   OR col2_cs LIKE 'needle2%'
   OR col3_cs LIKE '%nee%dle3%'

方法3：修改列的默认排序规则（需谨慎） #

如果业务场景中长期需要对这些列进行大小写敏感查询，可以直接修改列的默认排序规则，后续查询无需手动指定：

-- 修改列的默认排序规则（执行前需确认业务影响）
ALTER TABLE foobar
ALTER COLUMN col1 VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS;

ALTER TABLE foobar
ALTER COLUMN col2 VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS;

ALTER TABLE foobar
ALTER COLUMN col3 VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS;

-- 后续查询无需再指定排序规则
SELECT *
FROM foobar
WHERE col1 LIKE '%needle1%'
   OR col2 LIKE '%needle1%'
   OR col2 LIKE 'needle2%'
   OR col3 LIKE '%nee%dle3%'

注意：此方法会影响该列所有查询的默认排序规则，需评估业务兼容性后执行。

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