Teradata SQL多词模糊查询时如何在结果中显示匹配关键词

这里有两种实用的方法来实现你的需求，既能保留原有的多词模糊匹配逻辑，又能让结果显示每条记录对应的匹配关键词：

方法1：拆分条件为UNION ALL（支持多关键词匹配） #

如果你的数据存在单条记录匹配多个关键词的场景，这种方法会为每个匹配的关键词生成一条独立记录，能完整展示所有匹配关系：

SELECT 
    table1.survey_txt,
    'aaaa' AS matched_word
FROM table1
WHERE table1.survey_txt LIKE '%aaaa%'
UNION ALL
SELECT 
    table1.survey_txt,
    'bbbb' AS matched_word
FROM table1
WHERE table1.survey_txt LIKE '%bbbb%'
UNION ALL
SELECT 
    table1.survey_txt,
    'cccc' AS matched_word
FROM table1
WHERE table1.survey_txt LIKE '%cccc%'
UNION ALL
SELECT 
    table1.survey_txt,
    'dddd' AS matched_word
FROM table1
WHERE table1.survey_txt LIKE '%dddd%'
UNION ALL
SELECT 
    table1.survey_txt,
    'eeee' AS matched_word
FROM table1
WHERE table1.survey_txt LIKE '%eeee%'
UNION ALL
SELECT 
    table1.survey_txt,
    'ffff' AS matched_word
FROM table1
WHERE table1.survey_txt LIKE '%[^a-z0-9]ffff[^a-z0-9]%'

如果需要对重复的survey_txt进行去重（比如只保留第一个匹配的关键词），可以在最外层用窗口函数控制：

SELECT survey_txt, matched_word
FROM (
    -- 上面的UNION ALL查询内容
    SELECT 
        table1.survey_txt,
        'aaaa' AS matched_word,
        1 AS match_order
    FROM table1
    WHERE table1.survey_txt LIKE '%aaaa%'
    UNION ALL
    SELECT 
        table1.survey_txt,
        'bbbb' AS matched_word,
        2 AS match_order
    FROM table1
    WHERE table1.survey_txt LIKE '%bbbb%'
    -- 其他关键词的子查询依次添加对应的match_order
) t
QUALIFY ROW_NUMBER() OVER (PARTITION BY survey_txt ORDER BY match_order) = 1

方法2：使用CASE表达式（适合单关键词匹配场景） #

如果确定每条记录只会匹配一个关键词，或者你只需要返回第一个匹配的关键词，用CASE表达式会更简洁：

SELECT 
    table1.survey_txt,
    CASE
        WHEN table1.survey_txt LIKE '%aaaa%' THEN 'aaaa'
        WHEN table1.survey_txt LIKE '%bbbb%' THEN 'bbbb'
        WHEN table1.survey_txt LIKE '%cccc%' THEN 'cccc'
        WHEN table1.survey_txt LIKE '%dddd%' THEN 'dddd'
        WHEN table1.survey_txt LIKE '%eeee%' THEN 'eeee'
        WHEN table1.survey_txt LIKE '%[^a-z0-9]ffff[^a-z0-9]%' THEN 'ffff'
    END AS matched_word
FROM table1
WHERE table1.survey_txt LIKE ANY ( '%aaaa%', '%bbbb%', '%cccc%', '%dddd%', '%eeee%', '%[^a-z0-9]ffff[^a-z0-9]%' )

注意事项 #

• 关于%[^a-z0-9]ffff[^a-z0-9]%这个匹配模式：Teradata原生的LIKE并不支持正则表达式语法，如果你需要严格的非字母数字前后缀匹配，建议改用REGEXP_LIKE函数，比如REGEXP_LIKE(survey_txt, '[^a-z0-9]ffff[^a-z0-9]')，确保匹配逻辑符合预期。

内容的提问来源于stack exchange，提问作者Dan. Z