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

wh6knrhe  于 2022-12-11  发布在  Oracle
关注(0)|答案(3)|浏览(143)

我正在尝试编写一个查询来查找电话号码的特殊字符。
电话号码应为:2047653894
实际值:204765389(4204-7653894-2047653894(204)765389420476+53894 ......
注意:我只想查找包含特殊字符的电话号码,不想替换特殊字符。

b1uwtaje

b1uwtaje1#

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

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>
roejwanj

roejwanj2#

您可以将[[:punct:]] posix与REGEXP_REPLACE()沿着使用,例如

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

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

qxgroojn

qxgroojn3#

While you can use regular expressions, they are slow and it may be faster to use simple string functions and use TRANSLATE to find all the non-numeric characters and then replace them:

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

Which, for your sample data:

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;

Outputs:
| SIMPLIFIED_PHONE_NUMBER |
| ------------ |
| 2047653894 |
| 2047653894 |
| 2047653894 |
| 2047653894 |
| 2047653894 |
fiddle

Update

If you want to list phone numbers with non-digit characters then you can also use TRANSLATE to remove the digits and check if there are any other characters:

SELECT *
FROM   table_name
WHERE  TRANSLATE(phone_number, 'x0123456789', 'x') IS NOT NULL

you could also use REGEXP_LIKE to check that the string is not entirely digits:

SELECT *
FROM   table_name
WHERE  NOT REGEXP_LIKE(phone_number, '^\d+$')

or that there are non-digits:

SELECT *
FROM   table_name
WHERE  REGEXP_LIKE(phone_number, '\D')

However, regular expressions are probably going to be slower than simple string functions like TRANSLATE .
fiddle

相关问题