Concatenate two column values in Oracle sql

Question

I have two columns.

Values     Units

1.0           m
1.0 - 5.0       m
empty       null
10,15,20    Km

I wanted to concatenate the values and units.

But if i use the below select i am getting my result as

select
      case when unit is null then value
        else CONCAT(cast(value as varchar(100)), unit)
      end as new_value
    from table;

values
1.0 m
1.0 - 5.0 m
empty
10,15,20 km

I wanted it to be

 Values 
    1.0m
    1.0m - 5.0m
    empty
    10km,15km,20km.

Answer 1

would you try this code? "table1" is data preparation CTE(common table expression). you can just use the last select statement with regexp_replace function. '(\d+)' searches for digit group then adds the unit for each.

with table1("value", Units) as 
( 
select '1.0', 'm' from dual union all 
select '1.0 - 5.0', 'm' from dual union all 
select 'empty',  null from dual union all 
select '10,15,20', 'Km' from dual
) 
select regexp_replace("value",'(\d+\.\d+|\d+)','\1'||units) new_value
from table1;