This content has been marked as final. Show 4 replies
You can use REPLACE function
SQL> with test_1 as 2 ( 3 select 1 col_1, 'this is a string' col_2 from dual union all 4 select 2 col_1, 'this is a second string' col_2 from dual union all 5 select 3 col_1, 'this is frist string' col_2 from dual 6 ), 7 test_2 as 8 ( 9 select 1 col_1, 'is a' col_2, 'is not a' col_3 from dual union all 10 select 2 col_1, 'second' col_2, 'tenth' col_3 from dual union all 11 select 3 col_1, 'frist' col_2, 'first' col_3 from dual 12 ) 13 select 14 test_1.col_2, 15 replace(test_1.col_2, test_2.col_2, test_2.col_3) after 16 from 17 test_1, 18 test_2 19 where 20 test_1.col_1 = test_2.col_1; COL_2 AFTER ----------------------- ------------------------------ this is a string this is not a string this is a second string this is a tenth string this is frist string this is first string
Thanks, but that's not really what I had in mind. There is no equi join between the two tables. Notice in my result set each instance of 'is a' was replaced with 'is not a', not just the first record.
There probably is, but it would be easier in PL/SQL or a sql script because you have to loop through the second table for each row of the first table. Personally, I'd extract, transform and load, but that's just because I'm more comfortable in shell pattern matching languages which were designed for such things in the problem space I have.
i had a user fill up the arch log location in an hour when they used pl/sql looping through and updating. my goal was to have them do a create table nologging as select ...... to try to reduce the redo generation.