sqloracle-databaseconditional-statementsexasol

Fill rows in column A with value of column B if condition in column A is met

I have a table like:

colA    | colB
" "     | 1
"K 111" | 1
"K222"  | 2
" "     | 3

Some columns have only a space (" "), some have "K {number}", some have "K{number}".

If colA has only a space I want that value replaced with the one from colB.

So endresult should be:

colA    | colB
1       | 1
"K 111" | 1
"K222"  | 2
3       | 3

How can I do this?

Solution

  • You can use a case expression:

    select (case when colA = ' ' then to_char(col_b)
             else colA
        end) as new_colA

    If you wanted to be more general, you might use like:

    select (case when colA like 'K%' then colA
             else
        end) as new_colA

    In an update, you would move the when condition to a filtering condition:

    update t
    set colA = to_char(colb)
    where colA = ' ';