Pl sql Proc or update statement for address correction from abbreviation to fullform

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:

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 MERGE statement; easier to use than separate UPDATE + 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 (or DBMS_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.