is there any way to separate area code from phone number in oracle

Question

Is there any function on oracle who can give him phone number like a parameter and the output is : code area + phone number.

exemple :

parameter : +3323658568526 or 003323658568526 (french country code)

return :

• code area : +33
• phone number : 23658568526

Answer 1

You could use SUBSTR.

For example,

SQL> WITH sample_data AS(
  2  SELECT '+3323658568526' num FROM dual UNION ALL
  3  SELECT '003323658568526' num FROM dual
  4  )
  5  -- end of sample_data mimicking real table
  6  SELECT num,
  7    CASE
  8      WHEN SUBSTR(num, 1, 1) = '+'
  9      THEN SUBSTR(num, 1, 3)
 10      ELSE '+'
 11        ||ltrim(SUBSTR(num, 1, 4), '0')
 12    END area_code ,
 13    CASE
 14      WHEN SUBSTR(num, 1, 1) = '+'
 15      THEN SUBSTR(num, 4)
 16      ELSE SUBSTR(num, 5)
 17    END phone_number
 18  FROM sample_data;

NUM             AREA_ PHONE_NUMBER
--------------- ----- ------------
+3323658568526  +33   23658568526
003323658568526 +33   23658568526

SQL>

NOTE : The number of digits of area code may vary, in that case you need to handle it in the CASE expression. The better design would be to store them separately. You could normalize the data further.