Using Decode as a like statement in oracle

Question

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.

Answer 1

I'd use a case statement. Something like

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

Answer 2

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')