PostgreSQL 12：如何在SELECT语句中输出ANY()操作符内的可变长度匹配模式

这问题我之前也碰到过，固定截取长度的方法确实只适用于模式长度统一的情况，换成长短不一的模式就歇菜了。给你几个实用的方案，按需选择：

方案1：用CASE表达式逐个匹配（最直观） #

如果你的模式数量不多，直接用CASE判断每个模式的匹配情况是最直接的，不管模式长度多少都能精准对应：

SELECT 
    t.test1 AS test01,
    CASE
        WHEN t.test1 LIKE 'data_nr0%' THEN 'data_nr0'
        WHEN t.test1 LIKE 'data_nr1%' THEN 'data_nr1'
        WHEN t.test1 LIKE 'data_a%' THEN 'data_a'
        -- 新增模式的话在这里加对应的WHEN就行
    END AS parameter_
FROM my_test_table t 
WHERE t.test1 LIKE ANY('{data_nr0%,data_nr1%,data_a%}')

优点： #

• 逻辑清晰，一眼就能看懂每个分支对应的模式
• 性能好，WHERE已经过滤了符合条件的行，CASE的判断非常快
• 不需要额外的表或复杂函数

方案2：用CTE+JOIN批量匹配（适合模式较多的场景） #

如果后续要经常新增/修改模式，维护一堆CASE分支会很麻烦，这时候可以把模式放到临时数据集里，通过JOIN来匹配：

WITH patterns AS (
    SELECT 'data_nr0' AS pattern UNION ALL
    SELECT 'data_nr1' UNION ALL
    SELECT 'data_a'
    -- 新增模式就加一行SELECT
)
SELECT 
    t.test1 AS test01,
    p.pattern AS parameter_
FROM my_test_table t
JOIN patterns p ON t.test1 LIKE CONCAT(p.pattern, '%')
WHERE t.test1 LIKE ANY('{data_nr0%,data_nr1%,data_a%}')

注意： #

如果存在一个test01值能匹配多个模式的情况（比如同时匹配data和data_nr0），这个查询会返回多行重复的test01。如果你的模式是互斥的（比如不会有包含关系），那完全没问题；如果有重叠，可以加DISTINCT或者用ROW_NUMBER()来去重。

方案3：用正则表达式提取（简洁但适合模式少的情况） #

如果你的数据库支持正则表达式（比如PostgreSQL、MySQL 8.0+），可以用正则捕获匹配的前缀：

-- PostgreSQL示例
SELECT 
    t.test1 AS test01,
    (REGEXP_MATCH(t.test1, '^(data_nr0|data_nr1|data_a)'))[1] AS parameter_
FROM my_test_table t 
WHERE t.test1 LIKE ANY('{data_nr0%,data_nr1%,data_a%}')

-- MySQL示例
SELECT 
    t.test1 AS test01,
    REGEXP_REPLACE(t.test1, '^(data_nr0|data_nr1|data_a).*$', '$1') AS parameter_
FROM my_test_table t 
WHERE t.test1 LIKE ANY('{data_nr0%,data_nr1%,data_a%}')

这个方法用正则分组捕获开头的模式，直接提取出来，代码很简洁，但模式太多的话正则表达式会变得冗长，维护性不如前两个方案。

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