CODEWITHSUNDEEP
sqloracle-database
Stevenson
Stevenson

Reputation: 11

SQL PLSQL How to create a lookup on the values taken from the string column

I am noobie in PL/SQL and I am struggling with the problem below

My code is like:

select view1.col1 from view1

Col1 is the string column. The query retrieves following 2 records.

view1.col1
-----------------
Foo [40] boo boo foo [11] loo [abc]
Loo [2] foo foo [a31] foo boo [32]

there is another dictionary table for the numeric values present in the view1.col1

table 2

ID|Description|
1 | A
2 | G2
3 | C4
32| EE2
11| AA2
40| U1
99| ZZ6

I have another table which I wished to join in order to replace the only the numeric values in brackets [] in my example numbers [40], [11], [2], [32] with the descriptive value from table2.col2

I wished result of my code to retrieve 

-----------------
Foo U1 boo boo foo AA2 loo [abc]
Loo G2 foo foo [a31] foo boo EE2

Upvotes: 1

Views: 1088

Answers (1)

APC
APC

Reputation: 146239

It's a pretty tough problem for a beginner in PL/SQL. It requires several Oracle built-ins to get the right answer.

Anyway, here is a function which does it. It uses regular expressions to count, find and substitute the placeholders. Consequently performance may not be brilliant but it should be acceptable.

create or replace function substitution (p_str in varchar2) 
    return varchar2
as
    rv varchar2(4000);
    cnt pls_integer;
    l_id t2.id%type;
    l_sub t2.description%type;
begin
    rv := p_str;
    cnt := regexp_count(p_str, '\[([[:digit:]]+)\]');
    if cnt > 0 then
        for idx in 1..cnt loop
            l_id := to_number(rtrim(ltrim( regexp_substr(p_str, '\[([[:digit:]]+)\]', 1, idx ), '['),']'));
            select description 
            into l_sub
            from t2
            where id = l_id;
            rv := regexp_replace(rv, '\[([[:digit:]]+)\]', l_sub, 1, idx );
        end loop;
    end if;
    return rv;
end;
/

You call it like this:

select col1 as old_col
       , substitution(col1) as new_col
from view1;

Upvotes: 2

Related Questions