Multiple REPLACE in SQL-CodePudding

I would like to create a "view" table where I would like to rename 2 rows (2 different names). Unfortunately, when I type this command, it doesn't work:

SELECT torch_cooling AS MASTER,
       REPLACE (REPLACE(torch_cooling, 'gas', 'Gasgekühlt')'water', 'Wassergekühlt') AS TEXT
FROM to_torches

I would like "gas" to be "Gasgekühlt" and "Water" to be "Wassergekühlt".

SELECT torch_cooling AS MASTER,
       REPLACE (REPLACE(torch_cooling, 'gas', 'Gasgekühlt') 'water', 'Wassergekühlt') AS TEXT
FROM to_torches`

CodePudding user response：

I think REPLACE is not the right tool for this, consider using a CASE for this.

SELECT torch_cooling AS MASTER, 
       CASE torch_cooling 
            WHEN 'gas'   THEN 'Gasgekühlt'
            WHEN 'water' THEN 'Wassergekühlt'
       END AS TEXT
  FROM t;

This is not really scalable, if you want to scale this, you should use a mapping table.

CodePudding user response：

Beside the option to use a CASE WHEN construct for that (which is absolutely fine), you can also use a function like DECODE. Usually, your DB provides such a function.

The example according to your situation:

SELECT torch_cooling AS master, 
DECODE (torch_cooling ,'gas','Gasgekühlt', 'water','Wassergekühlt') AS text 
FROM to_torches;

Please note the exact syntax and function name for this way depend on your DB type since the function name differs.

DECODE will be used in Oracle DB's, but there are similar functions in other DB types, too.