多CLOB列子串匹配查询：获取匹配行及对应列方法问询

嘿，我来帮你捋清楚这个问题：你之前用ANY的方式不对，'%BLAKE%' = ANY(ename, job)是在做精确相等判断，不是模糊匹配，自然查不到结果。针对你有20个CLOB列的场景，我给你两种实用的实现方案：

方案一：OR组合LIKE+CASE标记匹配列 #

这种方式直白易懂，适合列数固定（20列）的场景：

SELECT 
    name,
    addr,
    -- 标记第一个命中的列（如果要显示所有命中列，看下面的变种）
    CASE
        WHEN text1 LIKE '%unemployed%' THEN 'text1'
        WHEN text2 LIKE '%unemployed%' THEN 'text2'
        -- 把text3到text19的判断依次加在这里
        WHEN text20 LIKE '%unemployed%' THEN 'text20'
        ELSE NULL
    END AS which_column_matched,
    text1, text2, ..., text20 -- 列出所有20个文本列
FROM your_table
WHERE 
    text1 LIKE '%unemployed%'
    OR text2 LIKE '%unemployed%'
    -- 同样把text3到text19的OR条件依次加在这里
    OR text20 LIKE '%unemployed%';

如果需要显示所有匹配的列（比如某行多个列都包含目标短语），可以用字符串拼接的方式：

SELECT 
    name,
    addr,
    -- 拼接所有命中的列名，去掉末尾多余的逗号
    TRIM(TRAILING ',' FROM 
        CASE WHEN text1 LIKE '%unemployed%' THEN 'text1,' ELSE '' END ||
        CASE WHEN text2 LIKE '%unemployed%' THEN 'text2,' ELSE '' END ||
        -- 依次添加text3到text19的拼接逻辑
        CASE WHEN text20 LIKE '%unemployed%' THEN 'text20,' ELSE '' END
    ) AS which_columns_matched,
    text1, text2, ..., text20
FROM your_table
WHERE 
    text1 LIKE '%unemployed%'
    OR text2 LIKE '%unemployed%'
    -- 依次添加text3到text19的OR条件
    OR text20 LIKE '%unemployed%';

方案二：用UNPIVOT转置列（更优雅的写法） #

如果觉得写20个OR太繁琐，用UNPIVOT把列转成行，查询逻辑会简洁很多：

WITH unpivoted_data AS (
    SELECT 
        name,
        addr,
        column_name,
        column_value
    FROM your_table
    UNPIVOT (
        column_value FOR column_name IN (
            text1, text2, ..., text20 -- 列出所有20个文本列
        )
    )
)
SELECT 
    name,
    addr,
    -- 用LISTAGG把所有命中的列名拼接成字符串
    LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name) AS which_columns_matched,
    -- 如果需要展示原列，可以通过关联查询拉回来
    (SELECT text1 FROM your_table t WHERE t.name = ud.name AND t.addr = ud.addr) AS text1,
    (SELECT text2 FROM your_table t WHERE t.name = ud.name AND t.addr = ud.addr) AS text2,
    -- 依次添加text3到text19的原列查询
    (SELECT text20 FROM your_table t WHERE t.name = ud.name AND t.addr = ud.addr) AS text20
FROM unpivoted_data ud
WHERE column_value LIKE '%unemployed%'
GROUP BY name, addr;

额外注意点： #

因为你的列是CLOB类型，不同数据库的处理略有差异：

• 如果你用Oracle，直接用LIKE可能会有性能问题，建议用DBMS_LOB.INSTR替代，比如WHEN DBMS_LOB.INSTR(text1, 'unemployed') > 0 THEN 'text1'，判断子串是否存在更高效。
• 如果你用PostgreSQL，CLOB对应TEXT类型，LIKE可以直接用，UNPIVOT可以换成UNNEST结合数组的写法。

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