I'm using Oracle 11g and I'm having trouble replacing multiple characters based on positions mentioned in a different table. For example:
Table 1
PRSKEY POSITION CHARACTER
123 3 ć
123 9 ć
Table 2
PRSKEY NAME
123 Becirovic
I have to replace the NAME in Table 2 to Bećirović.
I've tried regexp_replace but this function doesn't provide replacing more then 1 position, is there an easy way to fix this?
Here's another way to do it.
with tab1 as (select 123 as prskey, 3 as position, 'ć' as character from dual
union select 123, 9, 'ć' from dual),
tab2 as (select 123 as prskey, 'Becirovic' as name from dual)
select listagg(nvl(tab1.character, namechar)) within group(order by lvl)
from
(select prskey, substr(name, level, 1) as namechar, level as lvl
from tab2
connect by level <= length(name)
) splitname
left join tab1 on position = lvl and tab1.prskey = splitname.prskey
;
Simple solution using cursor ...
create table t1 (
prskey int,
pos int,
character char(1)
);
create table t2
(
prskey int,
name varchar2(100)
);
insert into t1 values (1, 1, 'b');
insert into t1 values (1, 3, 'e');
insert into t2 values (1, 'dear');
begin
for t1rec in (select * from t1) loop
update t2
set name = substr(name, 1, t1rec.pos - 1) || t1rec.character || substr(name, t1rec.pos + 1, length(name) - t1rec.pos)
where t2.prskey = t1rec.prskey;
end loop;
end;
/
I would prefer approach via PL/SQL, but in your tag only 'sql', so I made this monster:
with t as (
select 123 as id, 3 as pos, 'q' as new_char from dual
union all
select 123 as id, 6 as pos, 'z' as new_char from dual
union all
select 123 as id, 9 as pos, '1' as new_char from dual
union all
select 456 as id, 1 as pos, 'A' as new_char from dual
union all
select 456 as id, 4 as pos, 'Z' as new_char from dual
),
t1 as (
select 123 as id, 'Becirovic' as str from dual
union all
select 456 as id, 'Test' as str from dual
)
select listagg(out_text) within group (order by pos)
from(
select id, pos, new_char, str, prev, substr(str,prev,pos-prev)||new_char as out_text
from(
select id, pos, new_char, str, nvl(lag(pos) over (partition by id order by pos)+1,1) as prev
from (
select t.id, pos, new_char, str
from t, t1
where t.id = t1.id
) q
) a
) w
group by id
Result:
Beqirzvi1
AesZ
group byand addLISTAGG()function (for MySQL the same is GROUP_CONCAT). Need to order group members and cut strings to have multiple pieces. Should work but the query would be tricky. Easier to introduce a function.