I have this sql query which works well:

SELECT distinct
A,
CASE
WHEN Parameter = 'ID' and regexp_like (VALUE, 'FOO')
 THEN VALUE
  ELSE 'NA'
 END AS test FROM my_table;

my_table

result:

Now I would like to exclude VALUE that starts with 'BAR'. How can I add this to the CASE...WHEN statement?

The output should look this:

CodePudding user response：

Do NOT LIKE in the WHERE clause to skip the rows where value start with BAR.

SELECT distinct
A,
CASE
WHEN Parameter = 'ID' and regexp_like (VALUE, 'FOO')
 THEN VALUE
  ELSE 'NA'
 END AS test FROM my_table
WHERE value NOT LIKE 'BAR%'