Oracle: How to search and replace the text in column2 with exact value from the column1?

Question

I have 2 text columns in a table. The requirement is to compare these 2 columns and check whether any text is matching and replace it with the text from the first column. We have to watch for the case.

CREATE TABLE search_text(
id  NUMBER(20,0),
text1   varchar2(1000),
text2   varchar2(1000));

INSERT INTO search_text VALUES
(
 100,
 'Inband signaling used in transmission that reduces the available user bandwidth from 1.544 to 1.536 Mbps.',
 'USER Bandwidth >inband Signaling< (TRansmission>'
);

The expected outcome for text2 column after replace is

"user bandwidth >Inband signaling< (transmission>"

Answer 1

Using only SQL. Hierarchical query splits the string into multiple rows, and replaces word by word. Then an aggregate function gets the last row, that contains the accumulated replaces for all words:

with t (id, word, text1, text2, replaced_text2, lvl) as
(
select id,
       regexp_substr(text1, '[[:alpha:]\'']+', 1, 1, 'i'),
       text1,
       text2,
       regexp_replace(text2,
                      '([^[:alpha:]]+|^)' || regexp_substr(text1, '[[:alpha:]\'']+', 1, 1, 'i') || '([^[:alpha:]]+|$)',
                      '\1' || regexp_substr(text1, '[[:alpha:]\'']+', 1, 1, 'i') || '\2',
                      1, 1, 'i'),
       1 lvl
  from search_text
 union all
select s.id,
       regexp_substr(s.text1, '[[:alpha:]\'']+', 1, t.lvl + 1, 'i'),
       s.text1,
       s.text2,
       regexp_replace(t.replaced_text2, 
                      '([^[:alpha:]]+|^)' || regexp_substr(s.text1, '[[:alpha:]\'']+', 1, t.lvl + 1, 'i') || '([^[:alpha:]]+|$)',
                      '\1' || regexp_substr(s.text1, '[[:alpha:]\'']+', 1, t.lvl + 1, 'i') || '\2',
                      1, 0, 'i'),
       t.lvl + 1
  from search_text s, t
 where t.id = s.id
       and regexp_count(s.text1, '[[:alpha:]\'']+') >= t.lvl + 1
)
select t.id,
       t.text1,
       t.text2,
       max(t.replaced_text2) keep (dense_rank first order by t.lvl desc) replaced_text2
  from t
 group by t.id, t.text1, t.text2
 order by id;

Sample execution:

FSITJA@db01> with t (id, word, text1, text2, replaced_text2, lvl) as
  2  (
  3  select id,
  4         regexp_substr(text1, '[[:alpha:]\'']+', 1, 1, 'i'),
  5         text1,
  6         text2,
  7         regexp_replace(text2,
  8                        '([^[:alpha:]]+|^)' || regexp_substr(text1, '[[:alpha:]\'']+', 1, 1, 'i') || '([^[:alpha:]]+|$)',
  9                        '\1' || regexp_substr(text1, '[[:alpha:]\'']+', 1, 1, 'i') || '\2',
 10                        1, 1, 'i'),
 11         1 lvl
 12    from search_text
 13   union all
 14  select s.id,
 15         regexp_substr(s.text1, '[[:alpha:]\'']+', 1, t.lvl + 1, 'i'),
 16         s.text1,
 17         s.text2,
 18         regexp_replace(t.replaced_text2,
 19                        '([^[:alpha:]]+|^)' || regexp_substr(s.text1, '[[:alpha:]\'']+', 1, t.lvl + 1, 'i') || '([^[:alpha:]]+|$)',
 20                        '\1' || regexp_substr(s.text1, '[[:alpha:]\'']+', 1, t.lvl + 1, 'i') || '\2',
 21                        1, 0, 'i'),
 22         t.lvl + 1
 23    from search_text s, t
 24   where t.id = s.id
 25         and regexp_count(s.text1, '[[:alpha:]\'']+') >= t.lvl + 1
 26  )
 27  select t.id,
 28         t.text1,
 29         t.text2,
 30         max(t.replaced_text2) keep (dense_rank first order by t.lvl desc) replaced_text2
 31    from t
 32   group by t.id, t.text1, t.text2
 33   order by id;

  ID TEXT1                                    TEXT2                                    REPLACED_TEXT2
---- ---------------------------------------- ---------------------------------------- ----------------------------------------
 100 Inband signaling used in transmission th USER Bandwidth >inband Signaling< (TRans user bandwidth >Inband signaling< (trans
     at reduces the available user bandwidth  mission>                                 mission>
     from 1.544 to 1.536 Mbps.