CODEWITHSUNDEEP
sqloracle-database
user3485864
user3485864

Reputation: 53

SQL replace based on lookup table

How do I replace a string based on a lookup table?

I need to replace all instances of the Old_value from the Lookup_Table anywhere in the string in Column1 from Table1 with New_Value from the Lookup_Table.

For example:

Table1

"Column1"
'The Quick BC'
'The Quick BD'

Lookup_Table

"Old_value"  | "New_Value"
'BC'         | 'Brown Cat'
'BD'         | 'Brown Dog'

Desired_Output

"Column1"
'The Quick Brown Cat'
'The Quick Brown Dog'

Upvotes: 1

Views: 2577

Answers (1)

user5683823
user5683823

Reputation:

The obvious idea is to join the two tables on a LIKE condition. The outer join guarantees that inputs with no matches will be returned (with no changes made). However, this only works if each input string has AT MOST one substring found in the lookup table. For a more general solution, you will need a recursive query of some kind, as well as additional rules: What if both BC and BCD appear in the lookup table, and you have an input like ABCDE? Which gets priority - will you replace BC or BCD?

with
     Table1 ( Column1 ) as ( 
       select 'The Quick BC' from dual union all
       select 'The Quick BD' from dual
     ),
     Lookup_Table ( Old_value, New_Value) as (
       select 'BC', 'Brown Cat' from dual union all
       select 'BD', 'Brown Dog' from dual
     )
-- end of test data; the solution (SQL query) begins below this line
select column1, replace(t1.column1, lt.old_value, lt.new_value) as new_column1
from   table1 t1 left outer join lookup_table lt
                 on t1.column1 like '%' || lt.old_value || '%'
;


COLUMN1       NEW_COLUMN1
------------  -------------------
The Quick BC  The Quick Brown Cat
The Quick BD  The Quick Brown Dog

Upvotes: 2

Related Questions