Redshift正则匹配关联查询无匹配记录报错求助（禁用UDF）

问题分析 #

你尝试用正则匹配!~关联表a和表b时触发的错误，核心原因是!~作为正则匹配运算符，要求右侧的正则表达式必须是合法的UTF-8字面量。但表b的all_fruits是逗号分隔的字符串字段，可能包含正则特殊字符（如*、+、.等），导致正则解析失败。同时原SQL的逻辑也无法准确实现“返回无匹配记录”的需求——它会将a的每条记录与所有不匹配的b记录关联，产生大量冗余结果。

解决方案 #

我们可以用字符串定位函数替代正则匹配，既避免语法错误，又无需拆分all_fruits字段（适合表b数据量大的场景），同时调整逻辑满足需求：

场景1：获取a表中未出现在b表任何all_fruits列表中的记录 #

如果你的需求是找出a表中fruit从未在b表的任意all_fruits字段里出现的记录，使用NOT EXISTS子查询：

SELECT a.*
FROM a
WHERE NOT EXISTS (
    SELECT 1
    FROM b
    -- 前后拼接逗号，避免部分字符串匹配的误判（比如"app"匹配"apple"）
    WHERE position(',' || a.fruit || ',' in ',' || b.all_fruits || ',') > 0
)

场景2：获取left join后a的fruit不在对应b的all_fruits里的组合记录 #

如果你需要保留原left join的关联逻辑，仅修正匹配条件的错误，将ON子句改为字符串定位判断：

SELECT a.*, b.*
FROM a
LEFT JOIN b 
    ON position(',' || a.fruit || ',' in ',' || b.all_fruits || ',') = 0

方案说明 #

• position(substr, str)函数会返回子串substr在字符串str中的起始位置，找不到则返回0，完全不涉及正则解析，因此不会触发UTF-8字面量相关错误。
• 前后拼接逗号是为了实现精确匹配：比如确保a.fruit = 'apple'只会匹配all_fruits中完整的apple元素，而不会误匹配pineapple这类包含该字符串的元素。
• 无需拆分all_fruits字段，避免了拆分操作带来的性能损耗，适合处理大数据量的表b。

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