I have data in multiple columns
col_a | col_b | col_c
-----------------------------------------------
good car | medium sandwitch | good computer
bad computer | good wall | bad wall
and I want to select the first value that starts with "good" from these three columns
result
---------
good car
good wall
You can do this with a simple case expression:
select case
when col_a like 'good%' then col_a
when col_b like 'good%' then col_b
when col_c like 'good%' then col_c
end result
from table
This will evaluate in order, so order the columns in the case statement in whatever order you wish to check.
To remove the rows with no results, we have a few options:
Move this into a subquery (or CTE) with a where clause
select *
from
(
select case
when col_a like 'good%' then col_a
when col_b like 'good%' then col_b
when col_c like 'good%' then col_c
end result
from table
) a
where a.result is not null
Check all of them in your where clause
select case
when col_a like 'good%' then col_a
when col_b like 'good%' then col_b
when col_c like 'good%' then col_c
end result
from table
where col_a like 'good%'
or col_b like 'good%'
or col_c like 'good%'