OraFAQ Forum: SQL & PL/SQL » Decode statement

Home » SQL & PL/SQL » SQL & PL/SQL » Decode statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Decode statement [message #18617]

Thu, 07 February 2002 08:44

neel
Messages: 12
Registered: November 2001

Junior Member

I am trying to use Decode for doing this:
Select product_id, decode(prod_route,'A%',' ') from
products where prod_route is not null;
I am trying to say give me all the products where the prod_route is not null ,but at the same time i want the prod_route's like 'A%' to be shown as spaces.
What changes do i need to do. I guess decode is not able to recognize the 'A%'. Please do let me know the solution.

Thanks
Neel

Report message to a moderator

Re: Decode statement [message #18618 is a reply to message #18617]

Thu, 07 February 2002 08:52

Suresh Vemulapalli
Messages: 624
Registered: August 2000

Senior Member

Select product_id, (case when prod_route like 'A%' then ' ' else prod_route end) prod_route from
products where prod_route is not null;

Report message to a moderator

Re: Decode statement [message #18635 is a reply to message #18617]

Thu, 07 February 2002 23:16

Geoffrey
Messages: 32
Registered: February 2002

Member

you can do it like this :

Select
product_id,
decode(substr(prod_route,0,1),'A',' ',substr(prod_route,0,1)) from
products where prod_route is not null;

this will give you a ' ' if the prod_route starts with an A and give you the first letter of the prod_route if the first letter is not an A

Report message to a moderator

Previous Topic:	drop vs truncate.
Next Topic:	select query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 05:09:16 CDT 2024