Home » SQL & PL/SQL » SQL & PL/SQL » Help me with this query (Oracle 8i Enterprise )
Help me with this query [message #414822] Thu, 23 July 2009 12:45 Go to next message
bztom33
Messages: 95
Registered: June 2005
Member
Hi,

I do not want to output anything if a phone number is null.

Thanks,

Tom

create table a (
phone_num  varchar2(25)
)

insert into a values('9161234342');
insert into a values('');

select  '(' || SUBSTR(phone_num,1,3) || ')' || SUBSTR(phone_num,4,3) || '-' || SUBSTR(phone_num,4,4) PHONE_NUM from a


phone_num

(916)123-4342
()-     <--I do not want to show this if the phone_number is null

Re: Help me with this query [message #414823 is a reply to message #414822] Thu, 23 July 2009 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
WHERE phone_num is NOT NULL
Re: Help me with this query [message #414824 is a reply to message #414822] Thu, 23 July 2009 12:51 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Along with select and from clauses, oracle has where clause also.
You may need to add a filter phone_num IS NOT NULL.

By
Vamsi
Re: Help me with this query [message #414848 is a reply to message #414824] Thu, 23 July 2009 15:54 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
may be something LENGTH

with data as (
select 9161234342 as phone_num from dual
union all
select 1234342 as phone_num from dual
)

select 
case 
when length(phone_num)>8 then '(' || SUBSTR(phone_num,1,3) || ')' || SUBSTR(phone_num,4,3) || '-' || SUBSTR(phone_num,4,4)  
when length(phone_num)<8 then to_char(phone_num)
end
from data
Re: Help me with this query [message #414854 is a reply to message #414848] Thu, 23 July 2009 16:42 Go to previous message
bztom33
Messages: 95
Registered: June 2005
Member
Yes,thanks for helping me.

Previous Topic: Copy os file using pl/sql with different users credential (merged 6)
Next Topic: 'IN' operator problem
Goto Forum:
  


Current Time: Sat Dec 10 10:40:30 CST 2016

Total time taken to generate the page: 0.10086 seconds