Oracle SQL实现：列无指定字符串时返回NULL的方法

需求说明 #

当列中的字符串包含指定内容（如示例中的xyz）时返回该内容，不包含时返回NULL。

示例数据 #

预期输出 #

解决方案 #

可以使用CASE表达式结合INSTR函数实现，INSTR会返回子串在字符串中的起始位置，若不存在则返回0：

WITH sample_data AS (
    SELECT 'aaa-xyz-bbb' AS col FROM dual
    UNION ALL
    SELECT 'xyz-mmm-ooo' AS col FROM dual
    UNION ALL
    SELECT 'aaa-ttt-eee' AS col FROM dual
)
SELECT 
    CASE 
        WHEN INSTR(col, 'xyz') > 0 THEN 'xyz' 
        ELSE NULL 
    END AS result
FROM sample_data;

灵活匹配方案：正则表达式 #

如果需要忽略大小写等更灵活的匹配规则，可使用REGEXP_LIKE函数：

WITH sample_data AS (
    SELECT 'aaa-xyz-bbb' AS col FROM dual
    UNION ALL
    SELECT 'xyz-mmm-ooo' AS col FROM dual
    UNION ALL
    SELECT 'aaa-ttt-eee' AS col FROM dual
)
SELECT 
    CASE 
        WHEN REGEXP_LIKE(col, 'xyz', 'i') THEN 'xyz' -- 'i'参数表示忽略大小写
        ELSE NULL 
    END AS result
FROM sample_data;

说明 #

• INSTR(col, 'xyz') > 0：判断col列中是否存在xyz子串，存在则返回大于0的数值，条件成立。
• CASE表达式根据条件返回对应结果，不满足时返回NULL。

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