Home » SQL & PL/SQL » SQL & PL/SQL » Creating phone number format
Creating phone number format [message #435364] Tue, 15 December 2009 17:27 Go to next message
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 #435365 is a reply to message #435364] Tue, 15 December 2009 17:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>How can I make the phone number appear as: 111-555-1234 with the dashes (-) in sql.
They are NOT numbers.
use TO_CHAR & other string functions
Re: Creating phone number format [message #435366 is a reply to message #435364] Tue, 15 December 2009 18:17 Go to previous messageGo to next message
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 #435367 is a reply to message #435364] Tue, 15 December 2009 18:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>with the dashes (-) in sql.
Rhetorical question => How does Oracle differentiate between a dash character & the arithmetic minus operator?
Re: Creating phone number format [message #435369 is a reply to message #435367] Tue, 15 December 2009 19:21 Go to previous messageGo to next message
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 #435370 is a reply to message #435369] Tue, 15 December 2009 20:52 Go to previous messageGo to next message
eoracleapps
Messages: 17
Registered: November 2009
Location: California
Junior Member

Try
create table a1
(a NUMBER,
b NUMBER,
c NUMBER)

insert into a1 values (111,222,3333)

select a||'-'||b||'-'||c from a1


eoracleapps
Re: Creating phone number format [message #435371 is a reply to message #435370] Tue, 15 December 2009 20:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
eoracleapps wrote on Tue, 15 December 2009 18:52
Try
create table a1
(a NUMBER,
b NUMBER,
c NUMBER)

insert into a1 values (111,222,3333)

select a||'-'||b||'-'||c from a1


eoracleapps


insert into a1 values (001,002,0003)

select a||'-'||b||'-'||c from a1
what is result set?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Creating phone number format [message #435379 is a reply to message #435364] Tue, 15 December 2009 22:47 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Seeing all the post above with OP, /forum/fa/1578/0/
/forum/fa/5767/0/


@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 Go to previous messageGo to next message
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 #435430 is a reply to message #435423] Wed, 16 December 2009 04:14 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Sir,He wants the format like this

Quote:
xxx-xxx-xxxx

Not this Quote:
xxx-xxxxxxx


sriram Smile
Re: Creating phone number format [message #435435 is a reply to message #435430] Wed, 16 December 2009 04:23 Go to previous messageGo to next message
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 #435437 is a reply to message #435435] Wed, 16 December 2009 04:26 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes,sir.i agree.infact i am modifying that to include in my reply(The correct one).

Thank you sriram Smile
Re: Creating phone number format [message #435449 is a reply to message #435364] Wed, 16 December 2009 05:16 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL>r
  1  with tbl as
  2  (select 111 area ,5551234 pnum from dual
  3   union all
  4   select 111,5551122 from dual)
  5* select area || '-' || substr(pnum, 1, 3) || '-' || substr(pnum, 4) expr from tbl

EXPR
----------------------------------------------------------------------------------
111-555-1234
111-555-1122

2 rows selected.


regards,
Delna
Re: Creating phone number format [message #435489 is a reply to message #435449] Wed, 16 December 2009 07:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #435503 is a reply to message #435496] Wed, 16 December 2009 08:10 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Thanks. I also tried NVL but didn't seem to work:



select NVL(s_phone_area||'-'||substr(s_phone_number, -3)||'-'||substr(s_phone_number, 4),null) or ,' ') from s





Re: Creating phone number format [message #435505 is a reply to message #435364] Wed, 16 December 2009 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well no it wouldn't. The thing you're trying to nvl can never be null - it'll be ---
Re: Creating phone number format [message #435509 is a reply to message #435505] Wed, 16 December 2009 08:33 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Yes, the CASE worked.

Thanks
Re: Creating phone number format [message #435517 is a reply to message #435505] Wed, 16 December 2009 09:11 Go to previous messageGo to next message
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 #435519 is a reply to message #435517] Wed, 16 December 2009 09:21 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Since you concatenate hard-coded strings to (possible) null-values, the total will never be null, which is what cookiemonster tried to point out previously.
Re: Creating phone number format [message #435521 is a reply to message #435519] Wed, 16 December 2009 09:27 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I know I just wanted to show what else worked.
Re: Creating phone number format [message #435524 is a reply to message #435521] Wed, 16 December 2009 09:31 Go to previous messageGo to next message
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
Re: Creating phone number format [message #435527 is a reply to message #435364] Wed, 16 December 2009 09:53 Go to previous message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can do it with nvl2 but you'd have to repeat the concatenation. In which case I would suggest CASE is easier to follow.
Previous Topic: how can i Combine these 2 SQL statements into 1
Next Topic: sql query join
Goto Forum:
  


Current Time: Mon Feb 10 10:48:34 CST 2025