Oracle SQL ternary operator or function?

user_5507535 0
Viewed 3786

Is there a simple Oracle syntax like a ternary operator or function?

These work:

with
function qry(v in varchar2) return varchar2 is
   begin
      return owa_util.ite(v like ('%' || lower('something') || '%'),'Y','N');
   end;
select * from my_table where qry(my_col) = 'Y'

with
function qry(v in varchar2) return varchar2 is
   begin
      return case when v like('%' || lower('something') || '%') then 'Y' else 'N' end;
   end;
select * from my_table where qry(my_col) = 'Y'

If there's a simpler, shorter syntax I would like to know.

oracle19c if-statement conditional-operator oracle sql
1 Answers

You can use a CASE expression with LIKE:

SELECT *
FROM   my_table
WHERE  CASE WHEN my_col LIKE '%something%' THEN 'Y' ELSE 'N' END = 'Y';

or DECODE and INSTR:

SELECT *
FROM   my_table
WHERE  DECODE( INSTR( my_col, 'something' ), 0, 'N', 'Y' ) = 'Y';

or just simply use LIKE:

SELECT *
FROM   my_table
WHERE  my_col LIKE '%something%';

INSTR:

SELECT *
FROM   my_table
WHERE  INSTR( my_col, 'something' ) > 0;

or REGEXP_LIKE:

SELECT *
FROM   my_table
WHERE  REGEXP_LIKE( my_col, 'something' );

db<>fiddle here

user_15092640
Related