Formatting data in phone format in SELECT [message #239891] |
Wed, 23 May 2007 10:09 |
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 #239905 is a reply to message #239891] |
Wed, 23 May 2007 10:53 |
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 #240263 is a reply to message #239891] |
Thu, 24 May 2007 08:47 |
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!
|
|
|
|