Teradata中如何过滤列内含特殊字符的无效数据记录

你之前的过滤逻辑不生效，核心是两个写法错误：

• REGEXP_SUBSTR(column_name, '[0-9]+')的返回值是字段中匹配到的连续数字子串内容，不是匹配成功标记也不是匹配位置。比如异常值:1212121212调用这个函数，返回结果是1212121212，拿这个字符串和数字1做不等判断，完全达不到校验效果。
• 判断逻辑和需求反向：你需要排除空值，但原逻辑里column_name is null被放在保留条件里，会把空值错误保留下来。

根据你的表数据量，可以二选一：

正则匹配方案（写法简单易维护） #

用REGEXP_SIMILAR做整串规则校验，只保留从开头到结尾全为数字的记录，所有含特殊字符、字母、空格的异常值都会被过滤，包括你提到的开头带冒号的异常数据：

WHERE REGEXP_SIMILAR(column_name, '^[0-9]+$') = 1

语法说明：正则里^匹配字符串起始位置，$匹配字符串结束位置，加上这两个符号才能保证校验覆盖整个字符串，不会出现“串里有数字就通过”的误判。

内置函数方案（大表场景性能更优） #

如果表数据量在千万级以上，正则匹配性能不足，可以用Teradata原生字符串函数实现相同校验逻辑，没有正则回溯开销，执行速度更快：

WHERE 
  OCTET_LENGTH(
    TRANSLATE(column_name USING UNICODE_TO_UNICODE WITH delete_chars '0123456789')
  ) = 0
  AND column_name IS NOT NULL

逻辑说明：这段代码会先把字段值里所有数字字符全部删除，如果剩余字符串长度为0，说明原字段里没有任何非数字字符，属于合法号码；如果剩余长度大于0，就说明存在特殊字符等异常内容，会被过滤。

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