Format phone number in Oracle with country code

Question

I have a requirement to format phone numbers in the following way:

• No spaces
• No special characters
• Remove preceding zero - if area code exists
• Remove country code if present e.g. +44

For instance this: (03069) 990927 would become: 3069990927.

So far I have come up this this:

replace(replace(replace(replace(replace(replace(substr(replace(ltrim([VALUE],0), ' ', ''),nvl(length(substr(replace(ltrim([VALUE],0), ' ', ''),11)),0)+1), '-', ''), '(', ''), ')', ''),'/', ''), '.', ''), '+', '')

Is there a shorter version of this, maybe using a regular expression?

The final version of this snippet will become a column in a view that will return the following columns:

• Customer Number
• Customer Name
• Country
• Formatted Phone Number

The formatted phone number will be concatenated with the international dial code (e.g. +44) that are saved in the database in a table - DIALCODE_TAB(COUNTRY_CODE, CODE). Below is an example using the replace syntax above:

CREATE OR REPLACE FORCE VIEW "CUST_PHONE" ("CUSTOMER_ID", "NAME", "COUNTRY", "PHONE_NUMBER") AS 
  select 
    cicm.customer_id, 
    cicm.name, 
    dct.country, 
    dct.code || replace(replace(replace(replace(replace(replace(substr(replace(ltrim(cicm.value,0), ' ',   ''),nvl(length(substr(replace(ltrim(cicm.value,0), ' ', ''),11)),0)+1), '-', ''), '(', ''),   ')', ''),'/', ''), '.', ''), '+', '') phone_number 
from customer_info_comm_method cicm 
join dialcode_tab dct 
  on dct.country_code = customer_info_api.get_country_code(cicm.customer_id) 
where cicm.method_id_db = 'PHONE' 
  --and dct.code || replace(replace(replace(replace(replace(replace(substr(replace(ltrim(cicm.value,0), ' ',   ''),nvl(length(substr(replace(ltrim(cicm.value,0), ' ', ''),11)),0)+1), '-', ''), '(', ''),   ')', ''),'/', ''), '.', ''), '+', '') = [phone_number] 
--in terms of performance this SQL has to be written so that it returns all the records or a specific record when searching for the phone number - very quickly (<10s).
WITH read only;

N.B. A customer record can have more than 1 phone number and the same phone number can exist on more than 1 customer record.

Answer 1

To begin with a remark: This only works if the country is stored elsewhere for the record and there are no telephone numbers without an area code. Otherwise one would not be able to reconstruct the complete phone number again.

Then: How are country codes represented in your data? Is it always +44 or can it be 0044? Be careful here. Especially don't remove a single zero (assuming it's an area code), when it's actually the first of two zeros representing the country code :-)

Then: You need a list of all country codes. Let's take for example +1441441441. Where does the country code end? (Solution: +1441 is Bermudas.)

As to "no spaces" and "no special characters" you can solve this best with regexp_replace.

So all in all not so simple a task as you obviously expected it to be. (But not too hard to do either.)

I would use PL/SQL for this.

Hope my hints help you. Good luck.

EDIT: Here is what is needed. I still think a PL/SQL function will be best here.

Make sure your DIALCODE_TAB contains all country codes necessary.

1. Trim the phone number.
2. Then check if its starts with a country identifyer (+, 00).
2.1. If so: remove that. Remove all non-digits. Look up the country code in your table and remove it.
2.2. If not so: check if it starts with an area identifyer (0).
2.2.1. If so: remove it.
2.2.2. In any case: remove all non-digits.

That should do it, provided the numbers are valid. In Germany sometimes people write +49(0)40-123456, which is not valid, because one either uses a country code or an area code, not both in the same number. The (0) would have to be removed to make the number valid.

Answer 2

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE phone_numbers ( phone_number ) AS
          SELECT '(03069) 990927' FROM DUAL
UNION ALL SELECT '+44 1234 567890' FROM DUAL
UNION ALL SELECT '+44(0)1234 567890' FROM DUAL
UNION ALL SELECT '+44(012) 34-567-890' FROM DUAL
UNION ALL SELECT '+44-1234-567-890' FROM DUAL
UNION ALL SELECT '+358-1234567890' FROM DUAL;

Query 1:

If you are just dealing with +44 international dialling codes then you could:

• use ^\+44|\D to strip the +44 international code and all non-digit characters; then
• use ^0 to strip a leading zero if its present.

Like this:

SELECT REGEXP_REPLACE(
         REGEXP_REPLACE(
           phone_number,
           '^\+44|\D',
           ''
         ),
         '^0', '' ) AS phone_number
FROM   phone_numbers

Results:

|  PHONE_NUMBER |
|---------------|
|    3069990927 |
|    1234567890 |
|    1234567890 |
|    1234567890 |
|    1234567890 |
| 3581234567890 |

(You can see it doesn't work for the final number with a +358 international code.)

Query 2:

This can be simplified into a single regular expression (that's slightly less readable):

SELECT REGEXP_REPLACE(
         phone_number,
         '^(\+44)?\D*0?|\D',
         ''
       ) AS phone_number
FROM   phone_numbers

Results:

|  PHONE_NUMBER |
|---------------|
|    3069990927 |
|    1234567890 |
|    1234567890 |
|    1234567890 |
|    1234567890 |
| 3581234567890 |

Query 3:

If you want to deal with multiple international dialling codes then you will need to know which ones are valid (see http://en.wikipedia.org/wiki/List_of_country_calling_codes for a list).

This is an example of a regular expression which will strip out valid international dialling codes beginning with +3, +4 or +5 (I'll leave all the other dialling codes for you to code up):

SELECT REGEXP_REPLACE(
         phone_number,
         '^(\+(3[0123469]|3[57]\d|38[01256789]|4[013456789]|42[013]|5[09]\d|5[12345678]))?\D*0?|\D',
         ''
       ) AS phone_number
FROM   phone_numbers

Results:

| PHONE_NUMBER |
|--------------|
|   3069990927 |
|   1234567890 |
|   1234567890 |
|   1234567890 |
|   1234567890 |
|   1234567890 |

If the + at the start of the international dialling code is optional then just replace \+ (near the start of the regular expression) with \+?.

Answer 3

SELECT LTRIM(REGEXP_REPLACE(
                      REGEXP_REPLACE('+44(03069) 990927',
                                    '(\+).([[:digit:]])+'), -- to strip off country code
                     '[^[:alnum:]]'),-- Strip off non-aplanumeric [:digit] if only digit
             '0') -- Remove preceding Zero
FROM DUAL;

Wont work for +44990927 (If country code ends without any space or something or country didnt start with +)