查找电话号码特殊字符的Oracle SQL查询

Question

我正在尝试编写一个查询以查找电话号码的特殊字符。

期望电话号码为:2047653894

实际:204765389(4、204-7653894-2047653894,(204)7653894,20476+53894…

注意:我只想找到带有特殊字符的电话号码。我不想替换特殊字符

Answer 1

另一个选择是删除所有非数字(在这里，我住的地方，电话号码只有数字;我不是在说电话号码可能有的各种格式):

SQL> with test (col) as
2    (select '204765389(4'  from dual union all
3     select '204-7653894'  from dual union all
4     select '-2047653894'  from dual union all
5     select '(204)7653894' from dual union all
6     select '20476+53894'  from dual
7    )
8  select
9    col,
10    regexp_replace(col, 'D') result
11  from test;
COL          RESULT
------------ ------------------------------------------------
204765389(4  2047653894
204-7653894  2047653894
-2047653894  2047653894
(204)7653894 2047653894
20476+53894  2047653894
SQL>

---

[编辑]

如果您只想查找不包含数字的电话号码，请使用regexp_like:

SQL> with test (col) as
2    (select '204765389(4'  from dual union all
3     select '204-7653894'  from dual union all
4     select '-2047653894'  from dual union all
5     select '(204)7653894' from dual union all
6     select '20476+53894'  from dual union all
7     select '2047653897'   from dual
8    )
9  select col
10  from test
11  where regexp_like(col, 'D');
COL
------------
204765389(4
204-7653894
-2047653894
(204)7653894
20476+53894
SQL>

Answer 2

您可以将[[:punct:]]posix与REGEXP_REPLACE()一起使用，例如

SELECT REGEXP_REPLACE(col,'[[:punct:]]') AS col
FROM t

假设每个逗号分隔的值代表表中的一个列值

Answer 3

虽然您可以使用正则表达式，但它们很慢，使用简单的字符串函数并使用TRANSLATE查找所有非数字字符然后替换它们可能会更快:

SELECT TRANSLATE(
phone_number,
'0' || TRANSLATE(phone_number, 'x0123456789', 'x')
'0'
) AS simplified_phone_number
FROM   table_name;

对于您的样本数据:

CREATE TABLE table_name (phone_number) AS
SELECT '204765389(4' FROM DUAL UNION ALL
SELECT '204-7653894' FROM DUAL UNION ALL
SELECT '-2047653894' FROM DUAL UNION ALL
SELECT '(204)7653894' FROM DUAL UNION ALL
SELECT '20476+53894' FROM DUAL;

输出:

tbody> <<tr>

SIMPLIFIED_PHONE_NUMBER
2047653894
2047653894
2047653894
2047653894
2047653894