Home » SQL & PL/SQL » SQL & PL/SQL » Formatting data in phone format in SELECT
Formatting data in phone format in SELECT [message #239891] Wed, 23 May 2007 10:09 Go to next message
OraNewb
Messages: 4
Registered: May 2007
Junior Member
Hi,

Does anyone know how to format data into phone number format when Selecting it? I have a 10 digit number stored in my database and when I select it, I need to display it as 999-999-9999. I have tried everything I can think to try, including Regular Expressions, but I can't figure out a way to do this. If anyone has a suggestion, I would appreciate it.

Thanks.
Re: Formatting data in phone format in SELECT [message #239899 is a reply to message #239891] Wed, 23 May 2007 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create table t (col integer);

Table created.

SQL> insert into t values(9999999999);

1 row created.

SQL> select regexp_replace(to_char(col),'([0-9]{3})([0-9]{3})([0-9]{4})','\1-\2-\3')
  2  from t;
REGEXP_REPLACE(TO_CHAR(COL),'([0-9]{3})([0-9]{3})([0-9]{4})','\1-\2-\3')
-------------------------------------------------------------------------------------
999-999-9999

1 row selected.

Regards
Michel
Re: Formatting data in phone format in SELECT [message #239905 is a reply to message #239891] Wed, 23 May 2007 10:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
    select pno,
  2   substr(pno,1,3)||
  3  '-'||substr(pno,4,3)||
  4  '-'||substr(pno,7,4)
  5* from phone_format1
SQL> /

       PNO SUBSTR(PNO,1
---------- ------------
5614317567 561-431-7567
5614317567 561-431-7567
5614317567 561-431-7567
5614317567 561-431-7567
5614317567 561-431-7567
5614317567 561-431-7567
Re: Formatting data in phone format in SELECT [message #239921 is a reply to message #239905] Wed, 23 May 2007 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Too easy.
You don't like my regexp formula? Laughing

Regards
Michel
Re: Formatting data in phone format in SELECT [message #239937 is a reply to message #239891] Wed, 23 May 2007 12:46 Go to previous messageGo to next message
OraNewb
Messages: 4
Registered: May 2007
Junior Member
I love them both! Thank you so much! I already tried it in my application and it looks great, EXCEPT, it appears that I have some rows with a blank phone number. I have to select those rows regardless, but with the phone formatted, I get "--" when the number is blank. Any quick fix for that?

As you can see, I wasn't joking when I named myself OraNewb! Embarassed
Re: Formatting data in phone format in SELECT [message #239939 is a reply to message #239891] Wed, 23 May 2007 12:52 Go to previous messageGo to next message
OraNewb
Messages: 4
Registered: May 2007
Junior Member
One minute!!! Spoke too soon.... Michel, yours worked with no extra dashes on the null rows!!! You win! Thank you so much also, DreamzZ. I will use both examples in the future!
Re: Formatting data in phone format in SELECT [message #239942 is a reply to message #239937] Wed, 23 May 2007 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just add "where col is not null".

Regards
Michel
Re: Formatting data in phone format in SELECT [message #240028 is a reply to message #239942] Wed, 23 May 2007 23:51 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Using where col is not null would rule out the entire row. This is something completely different then showing a column as blank...
Re: Formatting data in phone format in SELECT [message #240054 is a reply to message #240028] Thu, 24 May 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You're right Frank, write too fast, should think before.
"decode" would be a better answer.

Regards
Michel
Re: Formatting data in phone format in SELECT [message #240263 is a reply to message #239891] Thu, 24 May 2007 08:47 Go to previous messageGo to next message
OraNewb
Messages: 4
Registered: May 2007
Junior Member
Yes, thanks. I did know enough to use a WHERE clause when necessary, but as Frank said, I knew it would exclude the entire row, which is not what I needed. However, the REGEXP_REPLACE did not insert the dashes if there was no data to replace, so that worked just fine. The SUBSTR example inserted the dashes every time so I ended up using the REGEXP instead.

thanks again!
Re: Formatting data in phone format in SELECT [message #240271 is a reply to message #240263] Thu, 24 May 2007 09:00 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
substr will be faster.
You can use "decode(col,null,null,substr...)"

Regards
Michel
Previous Topic: Datapump can't run in stored procedure
Next Topic: Trigger not working with empty fields
Goto Forum:
  


Current Time: Sun Dec 01 11:31:12 CST 2024