4 Replies Latest reply: Nov 19, 2012 8:15 AM by 889367 RSS

    translate from table of values

    889367
      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
          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
            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
              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
                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.