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开发者_JS百科 command like Decode that works this way?

Thanks.

I'd use a case statement. Something like

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

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

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