Creating phone number format [message #435364] |
Tue, 15 December 2009 17:27  |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
Hi trying to create a phone number format of: xxx-xxx-xxxx
from sql any suggestions?
Current query
select s_phone_area, s_phone_number
from s
S_PHONE_AREA,S_PHONE_NUMBER
111,5551234
111,5551122
How can I make the phone number appear as: 111-555-1234 with the dashes (-) in sql.
Thanks
Anne
|
|
|
|
Re: Creating phone number format [message #435366 is a reply to message #435364] |
Tue, 15 December 2009 18:17   |
rishg
Messages: 9 Registered: March 2008
|
Junior Member |
|
|
Assuming that your phone number strings have only commas inserted between them the phone number is stored completely in one column try this :
select substr(replace('999,999,9999', ',', ''), -3)||'-'||substr(replace('999,999,9999', ',', ''),4,3)||'-'||
substr(replace('999,999,9999', ',', ''),7)
from dual
If the values for area code and the remainder of the number is stored in 2 columns then try this :
select s_phone_area||'-'||substr(s_phone_number, -3)||'-'||substr(s_phone_number, 4) from s
|
|
|
|
Re: Creating phone number format [message #435369 is a reply to message #435367] |
Tue, 15 December 2009 19:21   |
yogeshse
Messages: 11 Registered: December 2005 Location: Chennai
|
Junior Member |
|
|
I think it depends on context "-" sign is used. It'll be treated as minus sign (-) when used in airthmatic expressions and a dash (-) when used as string or char. It's similar to function overriding. I guess.
Thank you !!
|
|
|
|
|
Re: Creating phone number format [message #435379 is a reply to message #435364] |
Tue, 15 December 2009 22:47   |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Seeing all the post above with OP, 

@Anne Simms,
Quote:Current query
select s_phone_area, s_phone_number
from s
S_PHONE_AREA,S_PHONE_NUMBER
111,5551234
111,5551122
Can you copy and paste the session containing output of the above query on SQL prompt? Because, I don't think, this is the correct output.
regards,
Delna
|
|
|
Re: Creating phone number format [message #435423 is a reply to message #435364] |
Wed, 16 December 2009 04:03   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you're on 10g, you an use regular expressions:with src as (select 111 area ,5551234 pnum from dual union all
select 111,5551122 from dual)
SELECT area||'-'||
REGEXP_REPLACE(pnum
,'([url=http://www.orafaq.com/wiki/:digit:]:digit:[/url]{3})([url=http://www.orafaq.com/wiki/:digit:]:digit:[/url]{4})'
,'\1-\2')
from src
|
|
|
|
Re: Creating phone number format [message #435435 is a reply to message #435430] |
Wed, 16 December 2009 04:23   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you'd taken 5 seconds to look at the query I posted, rather than leaping to the assumption that I'd posted a Regexp_Replace that didn't change the data in any way, you'd have noticed that my post got mauled by the REGEXP problem.
The original query should have been:
SQL> with src as (select 111 area ,5551234 pnum from dual union all
2 select 111,5551122 from dual)
3 SELECT area||'-'||
4 REGEXP_REPLACE(pnum
5 ,'([_[_:digit:_]_] {3})([_[_:digit:_]_] {4})'
6 ,'\1-\2') PNUM
7 from src;
with the underscore characters removed.
Rewriting it to avoid the problem gives:
with src as (select 111 area ,5551234 pnum from dual union all
select 111,5551122 from dual)
SELECT area||'-'||
REGEXP_REPLACE(pnum
,'([0-9]{3})([0-9]{4})'
,'\1-\2') pnum
from src;
PNUM
--------------------------------------------------------------------------------
111-555-1234
111-555-1122
[typo]
[Updated on: Wed, 16 December 2009 04:25] Report message to a moderator
|
|
|
|
|
Re: Creating phone number format [message #435489 is a reply to message #435449] |
Wed, 16 December 2009 07:56   |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
I appreciate all your help. I would like to learn how to use regular functions more.
One more question to add. If there is no phone number that appears from the columns like below, I don't want the dash line to appear I just want it to be null, so how can i fix that?
S_PHONE_AREA,S_PHONE_NUMBER
,
,
when using this function:
select s_phone_area||'-'||substr(s_phone_number, -3)||'-'||substr(s_phone_number, 4) from s
Anne
|
|
|
Re: Creating phone number format [message #435496 is a reply to message #435364] |
Wed, 16 December 2009 08:03   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
One way would be to use a case statement:
select (CASE WHEN s_phone_number IS NOT NULL
THEN s_phone_area||'-'||substr(s_phone_number, -3)||'-'||substr(s_phone_number, 4)
ELSE NULL
END) phone_no
FROM s;
|
|
|
|
|
|
Re: Creating phone number format [message #435517 is a reply to message #435505] |
Wed, 16 December 2009 09:11   |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
Actually NVL2 is cleaner..I forgot about this...
NVL2(s_phone_area||'-'||substr(s_phone_number, -3)||'-'||substr(s_phone_number, 4),null) from s
If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG.
|
|
|
|
|
Re: Creating phone number format [message #435524 is a reply to message #435521] |
Wed, 16 December 2009 09:31   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Too bad then that you have a syntax error in your example (only 2 arguments, where you need 3) plus that the example makes no sense.
So "show what worked" is not really what you did
|
|
|
|