How do I write a CASE statement with a wildcard match on a column
My Ver column has values Factual,Tableau021, Tableau022
The below is not working Open to any other workaround as well if CASE is not the right statement
CASE WHEN Ver = 'Tableau*' THEN 'get'
WHEN Ver = 'Factual' THEN 'Fact'
ELSE ''
END as Ver,
In SQL * is just another character. If you want to use it as a wildcard expression you must use LIKE and substitute the asterisk(*) with a percent(%) character like this:
with example_data as (
select "Tableau2" as Ver UNION ALL
select "test123" as Ver UNION ALL
select "Factual" as Ver
)
select Ver as old_ver,
CASE WHEN Ver like 'Tableau%' THEN 'get'
WHEN Ver = 'Factual' THEN 'Fact'
ELSE ''
END as Ver,
from example_data
This returns:
| old_ver | Ver |
|---|---|
| Tableau2 | get |
| test123 | '' (empty string) |
| Factual | Fact |
In case of having more than just two search values - CASE statement can become too verbose and hard to manage. In such cases consider below option
with example_data as (
select 'Tableau2' Ver union all
select 'test123' union all
select 'Factual'
), search_values as (
select 'Tableau%' value, 'get' result union all
select 'Factual', 'Fact'
)
select *, (
select result from search_values
where Ver like value limit 1
) result
from example_data