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

See if this helps: http://stackoverflow.com/questions/41925/is-there-a-standard-for-storing-normalized-phone-numbers-in-a-database — Vampiro, Jan 20 '16 at 10:15

score 1 · Accepted Answer · answered Jan 20 '16 at 09:27

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.

answered Jan 20 '16 at 09:27

Lalit Kumar B

47,486
13
97
124

Thank you Lalit, very nice solution. Exactly, the number of digits of area code vary from country to other. so i need to handle that with **CASE**, but i think i need more CASES to resolve the problem... and that my bigger the function.. what do you think about a table who contain area code?? – badaoui220 Jan 20 '16 at 09:50
@badaoui220 If you have a table containing area codes, then you could simply join the tables. The join condition would be the area code and the substr in my solution. – Lalit Kumar B Jan 20 '16 at 10:00
This works only when the country-code has two digits. However, it may have one, two or three digits. Also `00` is not always the prefix for abroad calls. For example when you call from U.S. to France you have to dial `011 33 ...` – Wernfried Domscheit Jan 20 '16 at 17:48
@WernfriedDomscheit Exactly what I mentioned in the note. And OP is aware of it. He has the area codes in a lookup table, which he could join. – Lalit Kumar B Jan 21 '16 at 05:10

score 0 · Answer 2 · answered Jan 20 '16 at 09:26

The best way to deal with this requirement is to have two separate columns for Area Code and Phone number. This helps with performance - as there is no need to perform substring operations which can negatively impact performance.

One more reason is the number of digits in the phone number may vary, so it may be difficult to find where the are code ends (unless you use a delimiter, which again leads to substring operations).

When you need to display the number in the user interface, you can concatenate and display.

Also helpful to run queries like "What is the area that most of our customers come from?" as you can do queries like

 SELECT AREA_CODE, COUNT(*) FROM TABLE GROUP BY AREA_CODE ORDER BY 2 DESC;

As Area Code does not have a lot different values, so having it as a leading column in the index also helps perform Index Skip Scans and Index Fast Full Scans.

Thanks for your answer, your design is so clear and logic on the case where we start a project from scratch, but now the project is already exist and we need to implement this solution to communicate with a WS :) — badaoui220, Jan 20 '16 at 09:41
Then use substring and add a function based index to compensate for performance. — Vampiro, Jan 20 '16 at 09:43

score 0 · Answer 3 · answered Jan 20 '16 at 09:50

You could even try with regular expressions:

SELECT REGEXP_REPLACE(
                      phone,
                      '(\+|00)([0-9][0-9])([0-9]*)',
                      '+\2'
                     ) AS AREA,
       REGEXP_REPLACE(
                      phone,
                      '(\+|00)([0-9][0-9])([0-9]*)',
                      '\3'
                     ) AS PHONE
  FROM (SELECT '003323658568526' phone FROM DUAL
        UNION ALL
        SELECT '+3323658568526' phone FROM DUAL);

Again, this assumes that your area codes always have 2 digits.

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

3 Answers3