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

CodePudding user response：

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.

--EDIT--

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%'