问用于格式化电话号码的oracle正则表达式

EN

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>

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