UPDATE tab1 SET budgpost_gr1=
CASE WHEN (budgpost in ('1001,1012,50055')) THEN 'BP_GR_A'
WHEN (budgpost in ('5,10,98,0')) THEN 'BP_GR_B'
WHEN (budgpost in ('11,876,7976,67465'))
create type tab_num is table of number;
create or replace function f_str_to_nums (i_str in varchar2) return tab_num is
v_tab_num tab_num := tab_num();
v_start number := 1;
v_end number;
v_delim VARCHAR2(1) := ',';
v_cnt number(1) := 1;
begin
v_end := instr(i_str||v_delim,v_delim,1, v_start);
WHILE v_end > 0 LOOP
v_cnt := v_cnt + 1;
v_tab_num.extend;
v_tab_num(v_tab_num.count) :=
substr(i_str,v_start,v_end-v_start);
v_start := v_end + 1;
v_end := instr(i_str||v_delim,v_delim,v_start);
END LOOP;
RETURN v_tab_num;
end;
/
Then you can use the function like so:
select column_id,
case when column_id in
(select column_value from table(f_str_to_nums('1,2,3,4'))) then 'red'
else 'blue' end
from user_tab_columns
where table_name = 'EMP'
CASE WHEN (budgpost in ('1001,1012,50055')) THEN 'BP_GR_A'
WHEN (budgpost in ('5,10,98,0')) THEN 'BP_GR_B'
WHEN (budgpost in ('11,876,7976,67465'))
ELSE 'Missing' END`;
create type tab_num is table of number;
create or replace function f_str_to_nums (i_str in varchar2) return tab_num is
v_tab_num tab_num := tab_num();
v_start number := 1;
v_end number;
v_delim VARCHAR2(1) := ',';
v_cnt number(1) := 1;
begin
v_end := instr(i_str||v_delim,v_delim,1, v_start);
WHILE v_end > 0 LOOP
v_cnt := v_cnt + 1;
v_tab_num.extend;
v_tab_num(v_tab_num.count) :=
substr(i_str,v_start,v_end-v_start);
v_start := v_end + 1;
v_end := instr(i_str||v_delim,v_delim,v_start);
END LOOP;
RETURN v_tab_num;
end;
/
Then you can use the function like so:
select column_id,
case when column_id in
(select column_value from table(f_str_to_nums('1,2,3,4'))) then 'red'
else 'blue' end
from user_tab_columns
where table_name = 'EMP'