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 ha

Solution 1:

You can use a case expression:

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

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

select (casewhen colA like'K%' then colAelseend) 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 =' ';

Solution 2:

You can use a case expression:

selectcasewhen cola = ' 'then to_char(colb) else cola end as cola,
    colb
from mytable

Note that all branches of a case expression must return values of the same datatype. It seems like colb is a number, so this converts it to a string.

Solution 3:

Or, DECODE function (just an alternative to CASE):

SQL>with test (cola, colb) as2    (select'K 111', 1from dual unionall3select' '    , 1from dual unionall4select'K222' , 2from dual unionall5select' '    , 3from dual
  6    )
  7select decode(cola, ' ', to_char(colb), cola) cola,
  8         colb
  9from test;

COLA             COLB
---------- ----------
K 111111
K222                233SQL>

Solution 4:

Yet another option is to update the value using IS NULL check as follows:

update your_table
   set colA = to_char(colB)
 wheretrim(colA) isnull;

Empty string in Oracle is considered as null.