Bdtjtk

I want to replace certain values with corresponding values in SQL. I cannot create a cross-reference table. The values can be specified in 'txt' or 'properties' file in my application

I looked at CASE statement but I have 200 replacements to make for which CASE is not sufficient

SELECT dummy FROM TestTable;

In my answer if I get LOT_NUMBER I want to replace it with Lot Number or if I get LOT_NUMBER_XREF I want to replace it with Lot Number Cross-Reference

dummy

LOT_NUMBER

LOT_NUMBER_XREF

to be replaced with

dummy

Lot Number

Lot Number Cross-Reference

If you have many different values to replace, you should create a table to store the mapping of old values to new values. Then you can join that table in your query.

The below SQL replaces the old value with the new value (or leaves it as is if no translation was defined) :

SELECT 

    COALESCE(m.new_value, t.value)

FROM 

    table t

    LEFT JOIN mapping_table m 

        ON t.value = m.old_value

For a limited number of values to translate you can CASE. or maybe a CTE with UNION :

WITH mapping_table AS (

    SELECT 'LOT_NUMBER' AS old_value, 'Lot Number' AS new_value

    SELECT 'LOT_NUMBER_XREF', 'Lot Number Cross-Reference'

)

SELECT 

    COALESCE(m.new_value, t.value)

FROM 

    table t

    LEFT JOIN mapping_table m 

        ON t.value = m.old_value

If you can't use cross-reference table, use (you'll have to generate it in your app) sub-query or cte instead!
CTE:

with mapping as (

 select 'LOT_NUMBER' as old_value, 'Lot Number' as new_value

 union all select 'LOT_NUMBER_XREF', 'Lot Number Cross-Reference'

)

select 

 t.value,

 coalesce(m.new_value, 'none') as new_value

from tbl_your_table t

left join mapping m on t.value = m.old_value

Or SubQuery:

select 

 t.value,

 coalesce(m.new_value, 'none') as new_value

from tbl_your_table t

left join (

 select 'LOT_NUMBER' as old_value, 'Lot Number' as new_value

 union all select 'LOT_NUMBER_XREF', 'Lot Number Cross-Reference'

) m on t.value = m.old_value

If you have something generic like _ charecter then what you can do is use LIKE operator and

             SELECT replace(dummy,'_',' ') from table where 

             dummy like '%_%'   -- check generic regex

then, use CASE WHEN on top of this with DUMMY IN(X,REF) Then REPLACE(DUMMY,'X',CROSS) likewise for the others as well this will reduce the no of cases.

The thing is just check the generalised terms and their replacements store it IN clause prefiltering the most general ones. If the count is still large you need a temporary table for lookup reference like array in other programming languages