CODEWITHSUNDEEP
sqlregexoracle-database
user1751356
user1751356

Reputation: 615

oracle regular expression to format phone number

I would like to use a case statement and regular expression combination to format phone number which is coming in below format

+1 (111) 111-1111 to 111-111-1111

and when the number of digits count after the first brace(c) is not equal to 10 then null else populate as is

Could you please provide me some useful Oracle regular expression functions to handle this

Thank you

Upvotes: 0

Views: 3185

Answers (2)

user5683823
user5683823

Reputation:

You don't need a CASE expression for this. You can use REGEXP_REPLACE and try to match the entire string. If a match is found, then use back references to reformat the number into the new model. If a match is not found, then the input string is returned as is.

with test (col) as
  (select '+2 (123) 123-4433' from dual union all -- convert
   select '22 (23)234-443'    from dual union all -- do not convert
   select '-3 (123) 345 2343' from dual union all -- do not convert
   select '123 345 565'       from dual union all -- do not convert
   select '+c (334) abc-1234' from dual           -- do not convert
  )
select col,
       regexp_replace(col, '^\+\d \((\d{3})\) (\d{3}-\d{4})$', '\1-\2') result
from   test
;

COL               RESULT              
----------------- --------------------
+2 (123) 123-4433 123-123-4433        
22 (23)234-443    22 (23)234-443      
-3 (123) 345 2343 -3 (123) 345 2343   
123 345 565       123 345 565         
+c (334) abc-1234 +c (334) abc-1234

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142788

For example:

SQL> with test (col) as
  2    (select '+2 (123) 123-4433' from dual union all -- convert
  3     select '22 (23)234-443'    from dual union all -- do not convert
  4     select '-3 (123) 345 2343' from dual union all -- do not convert
  5     select '123 345 565'       from dual union all -- do not convert
  6     select '+c (334) abc-1234' from dual           -- do not convert
  7    )
  8  select col,
  9         case when regexp_like(col, '\+\d \(\d{3}\) \d{3}-\d{4}') then
 10                   replace(substr(col, 5, length(col)), ') ','-')
 11              else col
 12         end result
 13  from test;

COL               RESULT
----------------- ----------------------------------------------------
+2 (123) 123-4433 123-123-4433
22 (23)234-443    22 (23)234-443
-3 (123) 345 2343 -3 (123) 345 2343
123 345 565       123 345 565
+c (334) abc-1234 +c (334) abc-1234

SQL>

Upvotes: 2

Related Questions