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

Question

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?

score 1 · Accepted Answer · answered Dec 02 '20 at 18:58

1

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 = ' ';

answered Dec 02 '20 at 18:58

Gordon Linoff

1,242,037
58
646
786

Thanks, first code example works perfectly. – Vega Dec 08 '20 at 14:28

score 0 · Answer 2 · answered Dec 02 '20 at 18:58

You can use a case expression:

select 
    case when 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.

Littlefoot · Answer 3 · 2020-12-02T20:46:21.717

0

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

SQL> with test (cola, colb) as
  2    (select 'K 111', 1 from dual union all
  3     select ' '    , 1 from dual union all
  4     select 'K222' , 2 from dual union all
  5     select ' '    , 3 from dual
  6    )
  7  select decode(cola, ' ', to_char(colb), cola) cola,
  8         colb
  9  from test;

COLA             COLB
---------- ----------
K 111               1
1                   1
K222                2
3                   3

SQL>

edited Dec 02 '20 at 20:46

answered Dec 02 '20 at 20:03

Littlefoot

131,892
15
35
57

NVL replaces a NULL. In the OP's data model, they use a single space for NULL. NVL doesn't deal with that. Perhaps you want to wrap `cola` within some sort of `NULLIF` call first. – Dec 02 '20 at 20:43
Ah, a *space* ... looking at the example, I was fooled by double quotes enclosing "nothing" and *translated* that (in my mind) as if it was a NULL. Completely wrong. Thank you, @mathguy. Rewritten to DECODE, just as an alternative to CASE. – Littlefoot Dec 02 '20 at 20:47

score 0 · Answer 4 · answered Dec 03 '20 at 02:52

0

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

update your_table
   set colA = to_char(colB)
 where trim(colA) is null;

Empty string in Oracle is considered as null.

answered Dec 03 '20 at 02:52

Popeye

35,427
4
10
31

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

4 Answers4

Linked