Format phone number in query 2

[Nunina]

Hi guys!

I have a field that holds telephone numbers. They can be in any format: 1234567890, 123-456-7890 or 456-7890. I need to format the field in a query to show the telephone as 123-456-7890.

I tried this in my query:

 to_char(G.TEF_CLIENTE1,'### ###-####')

, but I get an "Invalid format number model" error. Can you help?

Thanks a lot!

Nunina

 

[SantaMufasa]

Nunina,

Here is a function (Phone) and a couple of example uses:

create or replace function phone (rawphone in varchar2) return varchar2 is
begin
	if length(rawphone) = 10 then
		return	substr(rawphone,1,3)||' '||
			substr(rawphone,4,3)||'-'||
			substr(rawphone,7,4);
	elsif	length(rawphone) = 7 then
		return	substr(rawphone,1,3)||'-'||
			substr(rawphone,4,4);
	else	return 'Invalid #';
	end if;
end;
/

SQL> select phone(1234567) from dual;

PHONE(1234567)
----------------------------------------
123-4567
SQL> select phone(1234567890) from dual;

PHONE(1234567890)
----------------------------------------
123 456-7890
SQL> select phone(12345) from dual;

PHONE(12345)
----------------------------------------
Invalid #
SQL> 

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:17 (16Jan04) GMT, 08:17 (16Jan04) Mountain Time)

 

[Nunina]

Thanks Mufasa.

Although it is helpful (I saved it for future use), I wish to do something similar, but in a simple query.

This is my query:

Select cust_no, address_1, city, state, to_char(phone1, '### ###-####') from table_customers where cust_no = 1212

The mask part is not working and I get the error I mentioned before. I don't want to use a function because I'll be using the query to populate a data grid in a Visual Basic project.

Thanks again!

Nunina

 

[carp]

There are two problems here:
1. Embedded characters that may or may not be present.
2. Varying lengths.

I think the following query does what you are after (although it may not be as simple as you like; however, I think you are trying to make to_char do something it wasn't designed for):

SELECT DECODE(length(replace(replace(phone,'-',''),' ','')),
7,
substr(replace(replace(phone,'-',''),' ',''),1,3)||'-'||
substr(replace(replace(phone,'-',''),' ',''),4,4),
10,
substr(replace(replace(phone,'-',''),' ',''),1,3)||' '||
substr(replace(replace(phone,'-',''),' ',''),4,3)||'-'||
substr(replace(replace(phone,'-',''),' ',''),7,4),
'bad format: '||phone
) from my_table;

Here is my test data:
SQL> select * from my_table;

PHONE
---------------
7195551234
5551234
719 5551234
719-5551234
719-555-1234
719 555 1234

6 rows selected.
and here are the results of the query:

DECODE(LENGTH(REPLACE(REPLA
---------------------------
719 555-1234
555-1234
719 555-1234
719 555-1234
719 555-1234
719 555-1234

One other case that you might run into is parentheses around the area code. If this happens, you would also want to strip them out before checking the length of the phone number.

Elbert, CO
0904 MST

 

[SantaMufasa]

Nunina,

If there was a LEGAL format mask that would work for you, I would have used that instead of writing a function. Oracle does not offer a TO_CHAR() format model that does what you want. If you are prohibited from using a user-defined function, then here is your alternative:

accept rawphone prompt "Enter a rawphone number: "
select decode(length(&rawphone),
 10,substr(&rawphone,1,3)||' '||substr(&rawphone,4,3)||'-'||substr(&rawphone,7,4)
 ,7,substr(&rawphone,1,3)||'-'||substr(&rawphone,4,4)
   ,'Invalid #')
from dual
/
SQL> @ph2
Enter a rawphone number: 1234567890

DECODE(LENGT
------------
123 456-7890
SQL> @ph2
Enter a rawphone number: 1234567

DECODE(L
--------
123-4567
SQL> @ph2
Enter a rawphone number: 12345

DECODE(LE
---------
Invalid #
SQL>

Just replace the "&rawphone" string with the name of phone number column coming from your table.

Let me know if this is satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:14 (16Jan04) GMT, 09:14 (16Jan04) Mountain Time)

 

[Nunina]

Thanks to you both!!!

I combined a both posts and finally got the results I wanted. A star for you both!!

Nunina

 

[Nunina]

BTW, maybe we should post this in the FAQ, what do u guys think?

Nunina