CODEWITHSUNDEEP
oracle-databasedecodesql-like
Giancarlo Solarino
Giancarlo Solarino

Reputation: 155

Using Decode as a like statement in oracle

I need to write a sql statement like this: 

 SELECT id_segmento AS Segmento, Decode (id_segmento ,  '1' , 'a', 'b' )

 FROM mapchile.segmento

but in this case I will obtain an 'a' when id_segmento is equal to '1', I need it to be 'a' even when the string id_Segmento contains the '1', kind of like and like statment.

There is any other command like Decode that works this way?

Thanks.

Upvotes: 3

Views: 38636

Answers (3)

Jim Hudson
Jim Hudson

Reputation: 8079

I'd use a case statement. Something like

case
   when id_segmento like '%1%' then 'a'
   else 'b'
end

Upvotes: 9

Vincent Malgrat
Vincent Malgrat

Reputation: 67752

Use the CASE operator for complex evaluation instead of the DECODE function:

SELECT id_segmento AS Segmento, 
       CASE
          WHEN id_segmento LIKE '%1%' THEN 
            'a'
          ELSE
            'b'
       END
  FROM mapchile.segmento

Upvotes: 5

user533832
user533832

Reputation:

if you don't want to use case you can still use decode and instr:

decode(instr(id_segmento,'1'),0,'b','a')

I'm assuming you want to match on a '1' anywhere in the field. If you want to match on fields that start with a '1' then you could use:

decode(ascii(id_segmento),49,'a','b')

or

decode(substring(id_segmento,1,1),'1','a','b')

or

decode(instr(id_segmento,'1'),1,'a','b')

Upvotes: 5

Related Questions