SQL 找出不匹配某个格式的文本

SQL 找出不匹配某个格式的文本，你有一个文本字段，其中包含了一些格式化过的字符串（例如电话号码），你希望找出那些不符合格式要求的值。例如，你的数据如下所示。

SQL 找出不匹配某个格式的文本 问题描述

你有一个文本字段，其中包含了一些格式化过的字符串（例如电话号码），你希望找出那些不符合格式要求的值。例如，你的数据如下所示。

select emp_id, text
  from employee_comment
　
EMP_ID     TEXT
---------- ------------------------------------------------------------
7369       126 Varnum, Edmore MI 48829, 989 313-5351
7499       1105 McConnell Court
           Cedar Lake MI 48812
           Home: 989-387-4321
           Cell: (237) 438-3333

你希望把电话号码格式不正确的那些行都找出来。举例而言，查询结果应该包括下面这行数据，因为其中包含的那个电话号码同时使用了两种不同的分隔符。

7369       126 Varnum, Edmore MI 48829, 989 313-5351

你认为一个电话号码里包含的两个分隔符应该使用同样的符号。

SQL 找出不匹配某个格式的文本 解决方案

本问题的解决方案包含多个步骤。
(1) 设法定义何种形式的数据应该被认为“看起来像电话号码”。
(2) 删除格式正确的电话号码。
(3) 查看是否还剩下任何看起来像电话号码的数据。如果是，剩下的那些就是格式不正确的电话号码。
下面的解决方案充分利用了 Oracle Database 10g 提供的正则表达式功能。

select emp_id, text
  from employee_comment
 where regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')
   and regexp_like(
          regexp_replace(text,
            '[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}','***'),
          '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')
　
    EMP_ID TEXT
---------- ----------------------------------------------------------
      7369 126 Varnum, Edmore MI 48829, 989 313-5351
      7844 989-387.5359
      9999 906-387-1698, 313-535.8886

上面的每一行查询结果里都至少包含了一个看起来像电话号码而格式却不符合要求的值。

SQL 找出不匹配某个格式的文本 扩展知识

本解决方案的关键在于找出那些看起来像电话号码的数据。由于电话号码作为某个文本字段的一部分而存在，该字段包含的任何文本都可能是符合要求的电话号码。我们需要设法缩小搜寻范围，过滤掉那些明显不符合要求的数据。例如，我们不希望在查询结果中看到类似下面的数据。

    EMP_ID TEXT
---------- ----------------------------------------------------------
      7900 Cares for 100-year-old aunt during the day. Schedule only
           for evening and night shifts.

显然上述数据记录中根本不存在类似电话号码的内容，更别提格式是否有效了。每个人都能看明白这一点。问题是，我们应该如何让关系数据库管理系统也能“明白”这一点。相信你都希望知道具体的做法。请继续读下去吧。

　本实例源自 Jonathan Gennick 的一篇文章“Regular Expression Anti-Patterns”（已经取得作者授权）。

本解决方案使用下述 Pattern A 定义看起来像电话号码的数据。

Pattern A: [0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}

上述 Pattern A 规定了电话号码开头须包含两组 3 位长度的数字，然后须跟着一组 4 位长度的数字。两组之间的分隔符可以是英文破折号（-）、句号（.）或空格。当然，此处我们也能写出一个更复杂的正则表达式。例如，可以考虑允许出现 7 位长度的电话号码。不过，我们没必要偏离主题太远。现在的重点是设法定义何种形式的数据应该被认为“看起来像电话号码”，并且对于本问题而言，Pattern A 已经能够满足要求了。我们也可以写一个不同的正则表达式，不过它和 Pattern A 应该差别不大。
本解决方案的 WHERE 子句使用 Pattern A 确保只有那些可能包含电话号码（必须符合上述正则表达式！）的行才会被筛选出来。

select emp_id, text
  from employee_comment
 where regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')

接下来，我们需要考虑如何定义一个“格式良好的”电话号码。本解决方案使用 Pattern B 实现该操作。

Pattern B: [0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}

上述正则表达式里，\1 指代第一个子表达式 ([-. ])。必须和 \1 匹配到同样的分隔符。Pattern B 定义了何为格式良好的电话号码，这些电话号码会被排除掉（因为它们的格式符合要求）。本解决方案借助 REGEXP_REPLACE 函数来排除掉这些格式良好的电话号码。

regexp_replace(text,
   '[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}','***'),

上述 REGEXP_REPLACE 函数调用出现在 WHERE 子句里。任何格式良好的电话号码都会被三个连续的星号字符替换掉。同样，Pattern B 也不是一定要写成上述形式，只要它能筛选出符合要求的电话号码即可。
我们已经用三个连续的星号“***”替换掉了格式良好的电话号码，剩下的自然就是那些“看起来像电话号码”、但又不符合格式定义的电话号码了。接着针对 REGEXP_REPLACE 函数的返回值调用 REGEXP_LIKE 函数，这样就能筛选出不符合格式要求的电话号码。

and regexp_like(
       regexp_replace(text,
          '[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}','***'),
       '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')

Oracle 的正则表达式提供了文本模式匹配能力，没有该功能的话，本实例恐怕难以找到合适的解决方案。特别需要指出的是，本实例非常依赖 REGEXP_REPLACE 函数。其他数据库（例如 PostgreSQL）也支持正则表达式。但据我所知，只有 Oracle 同时支持正则表达式匹配和替换功能。