Teradata中基于字符串/正则搜索创建新列及代码有效性验证

需求梳理 #

你想要在column2和column3中搜索指定文本并生成新标记列，具体要求很明确：

• 对america做简单的包含式字符串搜索，只要任意列里有这个词就标记
• 对ACH要做精准的独立单词匹配，得匹配ACH、ACH.、ACH?这类，但要排除beach、peach这种把ACH当单词一部分的情况
• 期望的输出效果是：america标记列首行值为1（因为对应行的column2/3里有america），ach标记列末行值为1（因为对应行的column2里有独立的ACH）

你的原有代码问题分析 #

先看你写的这段SQL：

select column3, column2, 
CASE WHEN column3 like '%america%' THEN 1 WHEN column2 like '%america%' THEN 2 ELSE 0 END as Find , 
CASE WHEN REGEXP_SUBSTR(column3,'\bach\b')='audit' THEN 1 WHEN REGEXP_SUBSTR(column2,'\bach\b')='audit' THEN 2 ELSE 0 END as match2, 
(Find+match2) as summation 
from r00 where summation>1

这里有几个明显的问题需要修正：

1. 大小写敏感问题：默认情况下，LIKE和正则函数是区分大小写的（不同数据库规则有差异，比如MySQL默认不区分，Oracle默认区分）。如果要忽略大小写，得手动处理，比如把列和关键词都转成小写。
2. 正则逻辑完全错误：你写的REGEXP_SUBSTR(...)='audit'完全偏离需求了！我们要匹配的是ACH，不是audit，应该判断是否捕获到了符合要求的ACH匹配，而不是等于无关的单词。另外，不同数据库的单词边界语法不一样，比如Oracle不支持\b，得用其他写法。
3. 列别名不能直接复用：你在SELECT里定义了Find和summation，但WHERE子句里直接用summation>1会报错——数据库的执行顺序是先处理FROM/WHERE，再处理SELECT，所以WHERE里识别不了SELECT里的别名。
4. 标记逻辑和需求不符：你用1和2来区分america在column3还是column2，但需求里只需要标记“存在/不存在”（首行值为1），如果只是要判断是否存在，用1和0更贴合需求；如果需要区分来源，那1和2没问题，但得明确你的目标。

修正后的代码示例 #

下面分两种常用数据库给你修正后的代码，你可以根据自己用的数据库调整：

如果你用的是MySQL

MySQL支持\b作为单词边界，也可以用[[:<:]]和[[:>:]]来标记单词开头和结尾，忽略大小写的话可以用LOWER()或者正则修饰符：

SELECT 
    column3, 
    column2,
    -- 标记america是否存在，1=存在，0=不存在（忽略大小写）
    CASE 
        WHEN LOWER(column3) LIKE '%america%' OR LOWER(column2) LIKE '%america%' THEN 1 
        ELSE 0 
    END AS america_flag,
    -- 标记独立的ACH（忽略大小写，排除包含在单词里的情况）
    CASE 
        WHEN column3 REGEXP '[[:<:]]ACH[[:>:]]' COLLATE utf8mb4_general_ci 
             OR column2 REGEXP '[[:<:]]ACH[[:>:]]' COLLATE utf8mb4_general_ci THEN 1 
        ELSE 0 
    END AS ach_flag,
    -- 计算两个标记的和
    (
        CASE WHEN LOWER(column3) LIKE '%america%' OR LOWER(column2) LIKE '%america%' THEN 1 ELSE 0 END +
        CASE WHEN column3 REGEXP '[[:<:]]ACH[[:>:]]' COLLATE utf8mb4_general_ci OR column2 REGEXP '[[:<:]]ACH[[:>:]]' COLLATE utf8mb4_general_ci THEN 1 ELSE 0 END
    ) AS summation
FROM r00
-- 过滤summation>1的行，这里重复计算逻辑是因为不能直接用SELECT里的别名
WHERE (
    CASE WHEN LOWER(column3) LIKE '%america%' OR LOWER(column2) LIKE '%america%' THEN 1 ELSE 0 END +
    CASE WHEN column3 REGEXP '[[:<:]]ACH[[:>:]]' COLLATE utf8mb4_general_ci OR column2 REGEXP '[[:<:]]ACH[[:>:]]' COLLATE utf8mb4_general_ci THEN 1 ELSE 0 END
) > 1;

如果觉得重复计算麻烦，也可以用子查询包装一下：

SELECT *
FROM (
    SELECT 
        column3, 
        column2,
        CASE WHEN LOWER(column3) LIKE '%america%' OR LOWER(column2) LIKE '%america%' THEN 1 ELSE 0 END AS america_flag,
        CASE WHEN column3 REGEXP '[[:<:]]ACH[[:>:]]' COLLATE utf8mb4_general_ci OR column2 REGEXP '[[:<:]]ACH[[:>:]]' COLLATE utf8mb4_general_ci THEN 1 ELSE 0 END AS ach_flag,
        (america_flag + ach_flag) AS summation
    FROM r00
) t
WHERE summation > 1;

如果你用的是Oracle

Oracle不支持\b作为单词边界，得用(^|\W)匹配字符串开头或非单词字符，(\W|$)匹配非单词字符或字符串结尾，忽略大小写用'i'修饰符：

SELECT 
    column3, 
    column2,
    -- 标记america是否存在（忽略大小写）
    CASE 
        WHEN INSTR(LOWER(column3), 'america') > 0 OR INSTR(LOWER(column2), 'america') > 0 THEN 1 
        ELSE 0 
    END AS america_flag,
    -- 标记独立的ACH（忽略大小写）
    CASE 
        WHEN REGEXP_LIKE(column3, '(^|\W)ACH(\W|$)', 'i') 
             OR REGEXP_LIKE(column2, '(^|\W)ACH(\W|$)', 'i') THEN 1 
        ELSE 0 
    END AS ach_flag,
    -- 计算和
    (
        CASE WHEN INSTR(LOWER(column3), 'america') > 0 OR INSTR(LOWER(column2), 'america') > 0 THEN 1 ELSE 0 END +
        CASE WHEN REGEXP_LIKE(column3, '(^|\W)ACH(\W|$)', 'i') OR REGEXP_LIKE(column2, '(^|\W)ACH(\W|$)', 'i') THEN 1 ELSE 0 END
    ) AS summation
FROM r00
WHERE (
    CASE WHEN INSTR(LOWER(column3), 'america') > 0 OR INSTR(LOWER(column2), 'america') > 0 THEN 1 ELSE 0 END +
    CASE WHEN REGEXP_LIKE(column3, '(^|\W)ACH(\W|$)', 'i') OR REGEXP_LIKE(column2, '(^|\W)ACH(\W|$)', 'i') THEN 1 ELSE 0 END
) > 1;

同样，用子查询可以简化WHERE子句的写法。

核心知识点总结 #

• 简单包含搜索：用LIKE '%关键词%'就能实现，忽略大小写的话搭配LOWER()/UPPER()即可。
• 独立单词正则匹配：关键是用单词边界（不同数据库语法不同），确保匹配的是独立的目标词，而不是其他单词的一部分。
• 列别名复用技巧：如果想在WHERE里用SELECT定义的别名，要么重复计算逻辑，要么把查询包装成子查询/CTE，这样就能直接用别名过滤了。

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