Home » SQL & PL/SQL » SQL & PL/SQL » Use of Oracle Decode function (10g)
Use of Oracle Decode function [message #329532] Wed, 25 June 2008 12:56 Go to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hello Guys,

I want to know if i can achieve the below requirement with Decode function:

I am having a column with values like 'Cat - Food','Cat - Home', 'Dog - Food', 'Dog - Home'

I want to have the output something like this..

if column value starts with 'Cat - ' then the value should be 'Cat'
and if the value starts with 'Dog - ' then the value should be 'Dog'.

So the output will be Cat,Cat, Dog, Dog.

Am i clear?

thanks,
Mahesh
Re: Use of Oracle Decode function [message #329534 is a reply to message #329532] Wed, 25 June 2008 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must not use DECODE for this, you can use SUBSTR and INSTR, REGEXP_SUBSTR or CASE but DECODE is definitively not the good one (although it can be done along with SUBSTR and INSTR).

Regards
Michel
Re: Use of Oracle Decode function [message #329542 is a reply to message #329534] Wed, 25 June 2008 13:48 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
you can try this:


with tab as (
        select 'Cat - Food' a from dual
        union
        select 'Cat - Home'   from dual
        union
        select 'Dog - Food'   from dual
        union
        select 'Dog - Home' from dual)
select a
      ,case 
       when instr(a, 'Cat') > 0 then 'Cat' 
       when instr(a, 'Dog') > 0 then 'Dog'
       end b  
from tab

Re: Use of Oracle Decode function [message #329546 is a reply to message #329542] Wed, 25 June 2008 14:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since the original post says it starts with cat or dog, and those are the only two options, it would suffice to select the first three characters of the column. No decode, no case, no instr.
Re: Use of Oracle Decode function [message #329547 is a reply to message #329542] Wed, 25 June 2008 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong.
The requirement is "if column value starts with"

Regards
Michel
Re: Use of Oracle Decode function [message #329553 is a reply to message #329547] Wed, 25 June 2008 15:35 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Michel Cadot wrote on Thu, 26 June 2008 03:35
This is wrong.
The requirement is "if column value starts with"

Regards
Michel




if you mean Frank then, well that's something new! it's my first time to see a moderator disagree with the other!
Re: Use of Oracle Decode function [message #329556 is a reply to message #329553] Wed, 25 June 2008 16:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You cane see in the header of the post who Michel is replying to. He is replying to you. The point is, that all that is required (based on the OP's original scenario) is a simple substr of the first 3 characters of the original string, no need for the excessive coding that you supplied. However, let's face it, it is extremely unlikely that the scenario put forward by the OP is the actual situation, rather it will be an overly simplified example of the true problem to be solved.
Re: Use of Oracle Decode function [message #329558 is a reply to message #329532] Wed, 25 June 2008 16:50 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
thanks alot guys for your replies.

it solved using the CASE function.

Thanks,
Mahesh
Re: Use of Oracle Decode function [message #329565 is a reply to message #329556] Wed, 25 June 2008 20:49 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
oh well, my bad =(
Previous Topic: sqlplus hangs when running an SQL script
Next Topic: 2 subquery for INSERT INTO
Goto Forum:
  


Current Time: Tue Dec 06 06:28:45 CST 2016

Total time taken to generate the page: 0.14416 seconds