This discussion is archived
4 Replies Latest reply: Nov 19, 2012 6:15 AM by 889367 RSS

translate from table of values

889367 Newbie
Currently Being Moderated
oracle 10.2.0.5 ent ed

create table test_1 ( col_1 number, col_2 varchar2(50));

create table test_2 ( col_1 number, col_2 varchar2(15), col_3 varchar2(15));

insert into test_1 values (1, 'this is a string');
insert into test_1 values (2, 'this is a second string');
insert into test_1 values (3, 'this is frist string');

insert into test_2 values (1, 'is a', 'is not a');
insert into test_2 values (2, 'second', 'tenth');
insert into test_2 values (3, 'frist', 'first');

is there a way to write a create table as select from test_1 where the resulting data set would be like the following?

1 this is not a string
2 this is not a tenth string
3 this is first string

in other words replace any sub-string in test_1.col_2 that matches test_2.col2 with that value in test_2.col_3.

I was thinking translate would work by using select statements for the match & replace patterns, but realize that would return multiple records and wouldn't work.

Edited by: 886364 on Nov 16, 2012 10:35 AM
  • 1. Re: translate from table of values
    6363 Guru
    Currently Being Moderated
    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
  • 2. Re: translate from table of values
    889367 Newbie
    Currently Being Moderated
    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.
  • 3. Re: translate from table of values
    jgarry Guru
    Currently Being Moderated
    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.
  • 4. Re: translate from table of values
    889367 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points