Why is my CASE not matching in AWS Glue SQL Transform?

Question

I am trying to create a column_b depending on whether column_a contains a string such as 'string_1'. This is for a Transform - SQL Query node in AWS Glue. Here is a sample of my code.

select *
,CASE column_a
    WHEN (column_a LIKE '%_string_1%') THEN 'String 1'
    WHEN (column_b LIKE '%_string_2%') THEN 'String 2'
    END AS column_b
from myDataSource

Am I missing something?

I have tried to match the exact string and not whether it contains the string or not and still I am not able to catch the case.

Answer 1

That's not the correct syntax for using a searched case expression

You need:

select *,
  case
    when column_a like '%_string_1%' then 'String 1'
    when column_b like '%_string_2%' then 'String 2'
  end as column_b
from myDataSource;