SQL 'If and statement'?

Question

I need to augment a post translational file so that the product names in the file such that first column of example is changed to a consistent text format:

"PRODUCT1 1000MG" >> "PRODUCT 1 1000 MG"

"PRODUCT 2 1500MG" >> "PRODUCT 2 1500 MG"

"PRODUCT3 PLATED 200MG"  >> "PRODUCT 3 PLATED 200 MG"

And so on, with approx 50 lines of different products to be queried. The only thing I can think of is a complex nested IF statement .... ?

Thanks in advance!

Answer 1

If you can add tables you could use a translation table and an update with join:

oldval                  newval
"PRODUCT1 1000MG"       "PRODUCT 1 1000 MG"
"PRODUCT 2 1500MG"      "PRODUCT 2 1500 MG"
"PRODUCT3 PLATED 200MG" "PRODUCT 3 PLATED 200 MG"

The query would look somehow like this:

UPDATE tab
  SET val = t.newval
  FROM MyTable tab
    INNER JOIN Transl t ON t.oldval = tab.val