Hi there
I am trying write a solution using SQL. Following code will create sample tables and sample data.
drop table vals;
create table vals (attr_val varchar2(200));
insert into vals values ('Savings (Registered and non-registered);#Long Term Investment'); --> SV|LT
insert into vals values ('Income Generation'); --> IG
insert into vals values ('Leverage'); --> LV
insert into vals values ('Leverage;#Retirement Planning'); --> LV|RP
insert into vals values ('Long Term Investment;#Short Terms Investment;#Estate/Tax Planning'); --> LT|SI|EP
drop table codes;
create table codes (attr_val varchar2(200),
code varchar2(50)
);
insert into codes values ('Savings (Registered and non-registered)','SV');
insert into codes values ('Long Term Investment','LT');
insert into codes values ('Income Generation','IG');
insert into codes values ('Leverage','LV');
insert into codes values ('Retirement Planning','RP');
insert into codes values ('Short Terms Investment','SI');
insert into codes values ('Estate/Tax Planning','EP');
commit;
Here is what I am trying to get for the values stored in the "VALS" table (sample output):
'Savings (Registered and non-registered);#Long Term Investment', 'SV-LT'
'Income Generation', 'IN'
'Leverage', 'LV'
'Leverage;#Retirement Planning', 'LV-RP'
'Long Term Investment;#Short Terms Investment;#Estate/Tax Planning' ,'LT-SI-EP'
I want to replace all values in the "attr_val" column in the "VALS" table with corresponding codes from the "CODES" table.
For example, I want to replace 'Savings (Registered and non-registered);#Long Term Investment' (two values separated by ";#") with 'SV-LT'.
If there are more than two values in ATTR_VAL column (for example, line#5 in sample-output above), all should be replaced with corresponding codes from CODES table.
Can this be achieved using SQL or I have to write some PL/SQL code?
Any ideas?
Thanks in advance!