Have this case statement here that I want to use with both "CASE WHEN" and CASE ILIKE" similar to below:
SELECT
CASE coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] )(\/|$)', 1, 1, 'e', 1)), random_sample_column)
when 'green' then 'GO'
when 'red' then 'STOP'
when 'yellow' then 'SLOW'
else
case when coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ) ilike '%green' then 'GO?'
case when coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ) ilike '%yellow' then 'SLOW?'
case when coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ) ilike '%red' then 'STOP?'
END as sample_derived_column
FROM SAMPLE_TABLE_NAME;
Is there a way to write the (NESTED) ilike case statements similar to the 'Shorthand' being used in the 'CASE WHEN' statements at the top of the code block?
Thanks in advance!
Sample data here:
random_sample_column
--------------
yellow30-1123$%schmidt
jingle43123heimer
red
isthelightgreen
beluw
beow
blue
CodePudding user response:
One possible way is to use column aliases:
WITH cte AS (
SELECT *,
coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] )(\/|$)', 1, 1, 'e', 1)), random_sample_column) AS helper_col1,
coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ') AS helper_col2,
CASE helper_col1
when 'green' then 'GO'
when 'red' then 'STOP'
when 'yellow' then 'SLOW'
else
case when helper_col2 ilike '%green' then 'GO?'
when helper_col2 ilike '%yellow' then 'SLOW?'
when helper_col2 ilike '%red' then 'STOP?'
end
END as sample_derived_column
FROM SAMPLE_TABLE_NAME
)
SELECT * EXCLUDE (helper_col1, helper_col2)
FROM cte;
Nesting is not readable so:
WITH cte AS (
SELECT *,
coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] )(\/|$)', 1, 1, 'e', 1)), random_sample_column) AS helper_col1,
coalesce(lower(regexp_substr(random_sample_column, '\/key_word\/\\d \/([\\w-] ') AS helper_col2,
CASE
when helper_col1 = 'green' then 'GO'
when helper_col1 = 'red' then 'STOP'
when helper_col1 = 'yellow' then 'SLOW'
when helper_col2 ilike '%green' then 'GO?'
when helper_col2 ilike '%yellow' then 'SLOW?'
when helper_col2 ilike '%red' then 'STOP?'
END as sample_derived_column
FROM SAMPLE_TABLE_NAME
)
SELECT * EXCLUDE (helper_col1, helper_col2)
FROM cte;
EDIT:
In order to perform the simple case expression with LIKE
predicate and wildcard:
SELECT
CASE expresssion
WHEN ILIKE '%green' THEN 'GO?'
WHEN ILIKE '%yellow' THEN 'SLOW?'
WHEN ILIKE '%red' THEN 'STOP?'
END
FROM SAMPLE_TABLE_NAME;
the used SQL dialect has to implement optional SQL:2003 Extended CASE expression(F262)
feature.
More at: SQL 'CASE WHEN x' vs. 'CASE x WHEN' with greater-than condition?