we have 2 tables employee and address_check in oracle database. we have a scenario where we need to check whether person address is not full form in address columns and we need to check another table for the abbreviation and full_forms to replace abbreviation to full_forms . we have to do this on daily basics for the new employees . So we need an update or insert statement to this copy address_l1 column value to address_l1_c with full_form .
we have to create a insert/update statement or pl sql procedure where it should check person address columns and find out if any abbreviations are present and if abbreviation are present . It should check that abbreviation present in address_check table and find its full form and get that full form and replace it in address_l1_c .
example and sample data as below:
Advertisement
Answer
This is your sample data and the way to extract the abbreviation. This option presumes that it is the last “word” (w+$) in the address. If it is located elsewhere, you’ll get wrong result.
SQL> with test (name, address_l1) as 2 (select 'Tom', '330 Darrow Street' from dual union all 3 select 'John', '40 Pine CT' from dual union all 4 select 'Smith', '110 Varady ave' from dual 5 ) 6 select t.name, 7 t.address_l1, 8 regexp_substr(t.address_l1, 'w+$') abbrev 9 from test t; NAME ADDRESS_L1 ABBREV ----- ----------------- ---------- Tom 330 Darrow Street Street John 40 Pine CT CT Smith 110 Varady ave ave SQL>
Now, join extracted abbreviation with another table to get its full form. As it is unknown whether someone will enter “CT” or “Ct” or “ct”, I used the lower function to match those values. Outer join is used for abbreviations that don’t exist:
SQL> with 2 test (name, address_l1) as 3 (select 'Tom', '330 Darrow Street' from dual union all 4 select 'John', '40 Pine CT' from dual union all 5 select 'Smith', '110 Varady ave' from dual 6 ), 7 abbrev (abbreviation, full_Form) as 8 (select 'St', 'Street' from dual union all 9 select 'Ave', 'Avenue' from dual union all 10 select 'Dr', 'Drive' from dual union all 11 select 'Ct', 'Court' from dual 12 ) 13 select t.name, 14 t.address_l1, 15 regexp_substr(t.address_l1, 'w+$') abbrev, 16 a.full_form 17 from test t left join abbrev a on 18 lower(a.abbreviation) = lower(regexp_substr(t.address_l1, 'w+$')); NAME ADDRESS_L1 ABBREV FULL_F ----- ----------------- ---------- ------ Smith 110 Varady ave ave Avenue John 40 Pine CT CT Court Tom 330 Darrow Street Street SQL>
Finally, substitute abbreviations with their full forms (see comments within the code):
SQL> with 2 test (name, address_l1) as 3 (select 'Tom', '330 Darrow Street' from dual union all 4 select 'John', '40 Pine CT' from dual union all 5 select 'Smith', '110 Varady ave' from dual 6 ), 7 abbrev (abbreviation, full_Form) as 8 (select 'St', 'Street' from dual union all 9 select 'Ave', 'Avenue' from dual union all 10 select 'Dr', 'Drive' from dual union all 11 select 'Ct', 'Court' from dual 12 ) 13 select t.name, 14 t.address_l1, 15 -- abbreviation: 16 -- regexp_substr(t.address_l1, 'w+$') abbrev, 17 -- its full form: 18 -- a.full_form, 19 -- 20 -- position of the last space character in address string 21 -- regexp_instr(t.address_l1, '[^ ]+', 1, regexp_count(t.address_l1, ' ') + 1) i, 22 -- 23 -- concatenate the first part of the address (without the "abbreviation" ... 24 substr(t.address_l1, 25 1, 26 regexp_instr(t.address_l1, '[^ ]+', 1, regexp_count(t.address_l1, ' ') + 1) - 1 27 ) 28 || 29 -- ... with its full form if it exists. If not, use what you have 30 -- (this option covers the "Street" example in your test case) 31 nvl(a.full_form, regexp_substr(t.address_l1, 'w+$')) result 32 from test t left join abbrev a on 33 lower(a.abbreviation) = lower(regexp_substr(t.address_l1, 'w+$')); NAME ADDRESS_L1 RESULT ----- ----------------- --------------------------------------------------------------------------- ---------- Smith 110 Varady ave 110 Varady Avenue John 40 Pine CT 40 Pine Court Tom 330 Darrow Street 330 Darrow Street SQL>
This is the first part of the story. Note that it works for sample data; if it changes or doesn’t match what you posted, this code will have to be adjusted (for example, remove superfluous spaces).
As of:
we have to create a insert/update statement
- that would be a
MERGEstatement; easier to use than separateUPDATE+INSERT
For example:
SQL> merge into test t 2 using (select t.name, 3 substr(t.address_l1, 4 1, 5 regexp_instr(t.address_l1, '[^ ]+', 1, regexp_count(t.address_l1, ' ') + 1) - 1 6 ) 7 || 8 nvl(a.full_form, regexp_substr(t.address_l1, 'w+$')) result 9 from test t left join abbrev a on 10 lower(a.abbreviation) = lower(regexp_substr(t.address_l1, 'w+$')) 11 ) x 12 on (t.name = x.name) 13 when matched then update set 14 t.result = x.result 15 when not matched then insert (name, result) 16 values (x.name, x.result); 3 rows merged. SQL> select * From test; NAME ADDRESS_L1 RESULT ---------- ------------------------------ ---------------------------------------- Tom 330 Darrow Street 330 Darrow Street John 40 Pine CT 40 Pine Court Smith 110 Varady ave 110 Varady Avenue SQL>
we have to do this on daily basis
- use a scheduler, i.e. the
DBMS_SCHEDULER(orDBMS_JOB) built-in package. In order to use it, put your code into a stored procedure which will then be called by that utility.
In its simplest option, you’d put the above merge statement into a procedure as is:
create or replace procedure p_merge as begin merge into test ... end; /
Schedule it to run every day at noon:
SQL> declare 2 l_job number; 3 begin 4 dbms_Job.submit(l_job, 5 'p_merge;', 6 sysdate, 7 'trunc(sysdate) + 12/24' 8 ); 9 commit; 10 end; 11 / PL/SQL procedure successfully completed. SQL>
That’s all, more or less.