Map values in column to values in SQL

Question

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

Answer 1

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