Home » SQL & PL/SQL » SQL & PL/SQL » how to replace string against some key_value table? (oracle 10g on windows 2003)
how to replace string against some key_value table? [message #413995] Mon, 20 July 2009 01:20 Go to next message
kang
Messages: 89
Registered: November 2007
Member
how to replace string against some key_value table?

with tbl1 as(
select 'key1' key,'value1' value from dual union all
select 'key2','value2' from dual union all
select 'key3','value3' from dual
), tbl2 as(
select 'abc key1 xyz key2 123 key3' col from dual
)
select OOO from tbl2 t2 ~~~

desired output
--------------------------------
abc value1 xyz value2 123 value3
Re: how to replace string against some key_value table? [message #413996 is a reply to message #413995] Mon, 20 July 2009 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Wed, 04 March 2009 18:54
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
...

Number the lines in first table, use REPLACE for each row ordered by the number and returns only the result with the max number.

Regards
Michel

[Updated on: Mon, 20 July 2009 01:25]

Report message to a moderator

Re: how to replace string against some key_value table? [message #414001 is a reply to message #413996] Mon, 20 July 2009 01:48 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
sorry I'm not clear.
Re: how to replace string against some key_value table? [message #414007 is a reply to message #414001] Mon, 20 July 2009 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well recursive replace is not so simple.
You can do it using MODEL clause:
SQL> with
  2    tbl1 as (
  3      select 'key1' key,'value1' value from dual 
  4      union all
  5      select 'key2','value2' from dual 
  6      union all
  7      select 'key3','value3' from dual
  8    ), 
  9    tbl2 as ( select 'abc key1 xyz key2 123 key3' col from dual ),
 10    repl as (
 11      select co, rn, cnt
 12      from ( select level line from dual connect by level <= (select count(*) from tbl1) )
 13      model
 14        reference data on 
 15          ( select col, key, value, 
 16                   row_number() over (order by null) rn,
 17                   count(*) over () cnt
 18            from tbl2, tbl1 )
 19          dimension by (rn)
 20          measures (col, key, value, cnt)
 21        dimension by (line)
 22        measures (cast(null as varchar2(40)) co, 0 rn, 0 cnt)
 23        rules upsert all ( 
 24         co[ANY] = replace(nvl(co[cv(line)-1],data.col[1]),
 25                           data.key[cv(line)],data.value[cv(line)]),
 26         rn[ANY] = nvl(rn[cv()-1],0) + 1,
 27         cnt[ANY] = data.cnt[cv(line)]
 28        )
 29    )
 30  select co
 31  from repl
 32  where rn = cnt
 33  /
CO
----------------------------------------
abc value1 xyz value2 123 value3

1 row selected.

Regards
Michel
Re: how to replace string against some key_value table? [message #414020 is a reply to message #414001] Mon, 20 July 2009 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In SQL*Plus you can use a trick:
SQL> col rep new_value rep
SQL> with
  2    tbl1 as (
  3      select 'key1' key,'value1' value from dual 
  4      union all
  5      select 'key2','value2' from dual 
  6      union all
  7      select 'key3','value3' from dual
  8    ),
  9    data as (
 10      select key, value, 
 11             row_number() over (order by null) rn, 
 12             count(*) over () cnt
 13      from tbl1
 14    )
 15  select substr(max(sys_connect_by_path('replace','(')),2) ||
 16         '(col,' ||
 17         replace(substr(max(sys_connect_by_path(''''||key||''','''||value||''')','#')),2),'#',',')
 18           rep
 19  from data
 20  where rn = cnt
 21  connect by prior rn = rn-1
 22  start with rn = 1
 23  /
REP
-------------------------------------------------------------------------------------
replace(replace(replace(col,'key1','value1'),'key2','value2'),'key3','value3')

1 row selected.

SQL> with tbl2 as ( select 'abc key1 xyz key2 123 key3' col from dual )
  2  select &rep co
  3  from tbl2
  4  /
CO
--------------------------------
abc value1 xyz value2 123 value3

1 row selected.

Regards
Michel
Re: how to replace string against some key_value table? [message #414040 is a reply to message #414007] Mon, 20 July 2009 03:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very nice, but there is a non-recursive way to do it.
Split the line down into separate fields, check each one to see if it's in the replacement table, and if so replace it:
create table test_021 (key  varchar2(30), val varchar2(30));

create table test_022 (col  varchar2(100));

insert into test_021 values ('key1','value1');
insert into test_021 values ('key2','value2');
insert into test_021 values ('key3','value3');

insert into test_022 values ('abc key1 xyz key2 123 key3');


with tl as (select level lvl from dual connect by level <= 100)
    ,t2 as (select '|'||replace(col,' ','|')||'|' col from test_022)

select nvl((select val from test_021 where key = col),col) new_col
from (select lvl
            ,regexp_substr(col,'|[^|]+|',1,lvl)  col
      from   tl,t2
      where  lvl <= length(col)-length(replace(col,'|',''))-1);
;

NEW_COL
abc
value1
xyz
value2
123
value3

Glueing these back into a single string is left as an excercise for the reader.
Re: how to replace string against some key_value table? [message #414198 is a reply to message #413995] Mon, 20 July 2009 22:02 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
Thannks.

what if the rows to be replaced are more than one?

with
   tbl1 as (
     select 'key1' key,'value1' value from dual 
     union all
     select 'key2','value2' from dual 
     union all
     select 'key3','value3' from dual
   ), 
   tbl2 as ( select 'abc key1 xyz key2 1235 key3' col from dual 
             union all
             select 'abc key1 xyz key2 1234 key3' col from dual
   ),
   repl as (
     select co, rn, cnt
     from ( select level line from dual connect by level <= (select count(*) from tbl1) )
     model
       reference data on 
         ( select col, key, value, 
                  row_number() over (order by null) rn,
                  count(*) over () cnt
           from tbl2, tbl1 )
         dimension by (rn)
         measures (col, key, value, cnt)
       dimension by (line)
       measures (cast(null as varchar2(40)) co, 0 rn, 0 cnt)
       rules upsert all ( 
        co[ANY] = replace(nvl(co[cv(line)-1],data.col[1]),
                          data.key[cv(line)],data.value[cv(line)]),
        rn[ANY] = nvl(rn[cv()-1],0) + 1,
        cnt[ANY] = data.cnt[cv(line)]
       )
   )
 select co
 from repl
 where rn = cnt
Re: how to replace string against some key_value table? [message #414202 is a reply to message #414198] Mon, 20 July 2009 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Left to you as an exercise... What did you try to solve it?

Regards
Michel
Re: how to replace string against some key_value table? [message #414314 is a reply to message #414202] Tue, 21 July 2009 09:31 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
really tough.

I'd better study the keywords first.

Thanks.
Re: how to replace string against some key_value table? [message #414545 is a reply to message #414314] Wed, 22 July 2009 09:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What's the problem? All I've done in this example is to add the original (before split) value of the column, and as you can see, it returns all the values correctly:
create table test_021 (key  varchar2(30), val varchar2(30));

create table test_022 (col  varchar2(100));

insert into test_021 values ('key1','value1');
insert into test_021 values ('key2','value2');
insert into test_021 values ('key3','value3');

insert into test_022 values ('abc key1 xyz key2 123 key3');
insert into test_022 values ('addc key2 xwwwyz key1 112123 key3');


with tl as (select level lvl from dual connect by level <= 100)
    ,t2 as (select '|'||replace(col,' ','|')||'|' col from test_022)
select orig_col,nvl((select val from test_021 where key = col),col) new_col
from (select t2.col orig_col
            ,lvl
            ,regexp_substr(col,'|[^|]+|',1,lvl)  col
      from   tl,t2
      where  lvl <= length(col)-length(replace(col,'|',''))-1)
order by orig_col,lvl;
Re: how to replace string against some key_value table? [message #415883 is a reply to message #414545] Wed, 29 July 2009 11:15 Go to previous message
kang
Messages: 89
Registered: November 2007
Member
don't you think the result should be two rows?
Previous Topic: how to select all packages
Next Topic: how to write this mssql query in oracle
Goto Forum:
  


Current Time: Wed Dec 07 14:53:20 CST 2016

Total time taken to generate the page: 0.10403 seconds