I am trying to write a query to find special character for phone numbers.

Expected phone number is : 2047653894

Actual: 204765389(4, 204-7653894, -2047653894, (204)7653894, 20476 53894, ....

NOTE: I only want to find the phone numbers with special characters. I don't want to replace special characters.

CodePudding user response：

Another option is to remove all non-digits (here, where I live, phone numbers are digits only; I'm not talking about various formats phone numbers might have):

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>

CodePudding user response：

You can use [[:punct:]] posix along with REGEXP_REPLACE() such as

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

assuming each comma-separated value represents a column value within a table

Demo

CodePudding user response：

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