OraFAQ Forum: SQL & PL/SQL » Can we use like operator in Decode Query

Home » SQL & PL/SQL » SQL & PL/SQL » Can we use like operator in Decode Query

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

Can we use like operator in Decode Query [message #34263]

Thu, 02 December 2004 00:50

santosh
Messages: 85
Registered: October 2000

Member

Version - Oracle 8i

How to build the query where in decode with "like" operator?

Eg. Select decode(col1,'%North%','North','%South%','South','Central') from Temp;

Is�it possible to find and decode col1 with values Delhi - North to North�or Chennai - South to South??

Is there any function available??

Thanks in advance.

�

�

Report message to a moderator

Re: Can we use like operator in Decode Query [message #34268 is a reply to message #34263]

Thu, 02 December 2004 02:16

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Use a combination of INSTR and SIGN:

SELECT col1
,      DECODE(+1
       ,      SIGN(INSTR(UPPER(col1),'NORTH')), 'North'
       ,      SIGN(INSTR(UPPER(col1),'SOUTH')), 'South'
       ,      'Central')                        region
FROM   temp       
/

Note, if col1 contains both North and South, this code will return 'North' because it appears first in the DECODE.

Report message to a moderator

Re: Can we use like operator in Decode Query [message #34280 is a reply to message #34263]

Thu, 02 December 2004 13:19

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

You could also try CASE:

SELECT CASE
           WHEN col1 LIKE '%North%' THEN 'North'
           WHEN col1 LIKE '%South%' THEN 'South'
           ELSE 'Central'
       END
FROM   temp;

Report message to a moderator

Previous Topic:	SQL*LOADER Control Script
Next Topic:	Dynamic SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 23 19:25:06 CDT 2025