Phone Number format 2

[fmrock]

I have a query that I am working on and part of it pulls together 3 fields into the phone number.

PHONE_AREA ||'-'|| PHONE_PRE || '-' || PHONE_EXT "Phone"

Lots of the rows do not have any information and the results show "--". Does anyone have any formating techniques for phone numbers and issues with this?

Thanks,

 

[karluk]

Something like the following would probably work - you would concatenate the '-' only if the columns are not null.

nvl2(PHONE_AREA, PHONE_AREA||'-', PHONE_AREA)||
nvl2(PHONE_PRE,  PHONE_PRE ||'-', PHONE_PRE) || PHONE_EXT

 

[ChrisHunt]

I'd do this:

LTRIM(RTRIM(
   PHONE_AREA ||'-'|| PHONE_PRE || '-' || PHONE_EXT,
   '-'),'-')

That'll trim any unused hyphens off the beginning or end of the number, and trim them all if all fields are null.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

...And yet another alternative:

SELECT replace(to_CHAR('1234567890','999,999,9999'),',','-') phone from dual;

PHONE
-------------
123-456-7890

SELECT replace(to_CHAR('','999,999,9999'),',','-') phone from dual;

PHONE
-------------

SQL>

Notice that it even accommodates nulls as you would want them to appear.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

Thanks for all the responses.

Santamufasa, where in yours would the field names go?

Should they all be put together where you have 1234567890?

 

[SantaMufasa]

Yes, and here is the proof-of-concept:

select * from fmrock;

PHONE_AREA  PHONE_PRE  PHONE_EXT
---------- ---------- ----------
       123        456       7890

       212        555       1212
       800        555       3344

4 rows selected.

SELECT replace(to_CHAR(phone_area||phone_pre||phone_ext,'999,999,9999'),',','-') phone
  from fmrock;

PHONE
-------------
 123-456-7890

 212-555-1212
 800-555-3344

Let us know your thoughts or questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

Sounds good, only thought is will there be any problems, if only the area code is filled out and not any of the other phone fields?

 

[SantaMufasa]

Let bad data live as short a life as possible.

The above means that your data-entry mechanism should not allow "bad" phone numbers to enter the database.


Since a phone number with just PHONE_AREA is, I'm sure, a business-data exception, then I suggest you run a one-time data scrubber that NULLs out all three phone-number components if there are any known-to-be-bad phone numbers.

will there be any problems, if only the area code is filled out and not any of the other phone fields?

One test is worth 100 expert opinions.

As far as my code is concerned, here is the result if only PHONE_AREA has a value:

SQL> select * from fmrock;

PHONE_AREA  PHONE_PRE  PHONE_EXT
---------- ---------- ----------
       123        456       7890

       212        555       1212
       800        555       3344
       888

5 rows selected.

SELECT replace(to_CHAR(phone_area||phone_pre||phone_ext,'999,999,9999'),',','-') phone from fmrock;

PHONE
-------------
 123-456-7890

 212-555-1212
 800-555-3344
          888

5 rows selected.

If, instead of scrubbing your data, you want to allow bad data to live, but generate an error message, you could amend your code to behave this way:

col phone format a35
SELECT replace(to_CHAR(phone_area||phone_pre||phone_ext,'999,999,9999'),',','-')||
       decode (length (phone_area||phone_pre||phone_ext)
       ,null,null -- if no  values exist, output nothing
       ,10,null   -- if all values exist, output nothing
       ,' <- Missing digits') -- Otherwise, display error
       phone
  from fmrock;

PHONE
-----------------------------------
 123-456-7890

 212-555-1212
 800-555-3344
          888 <- Missing digits

5 rows selected.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

Thanks for your help Santa, I do agree with you, but this is a third party system, that allows the data to be entered this way.

Thanks again for your help.