[SOLVED] Replacing multiple characters in a CASE statement, SQL

Replacing multiple characters in a CASE statement, SQL

I would like to change a bunch of characters in a column using a CASE statement in SQL. The code is the following

CASE
     WHEN [EmpName] LIKE '%æ%' 
       THEN REPLACE([EmpName], 'æ', 'ae')

     WHEN [EmpName] LIKE '%ø%'
       THEN REPLACE([EmpName], 'ø', 'oe') 

     WHEN [EmpName] LIKE '%å%'
       THEN REPLACE([EmpName], 'å', 'aa')

     WHEN [EmpName] LIKE '%-%'
       THEN REPLACE([EmpName], '-', '')

     ELSE [EmpName]
END [EmpName (no special characters)]

The problem is that if a name has multiple characters of a different kind (e.g. ø and å), it will only replace only one of them (the first one in the case statement that is ø). Is there any way to replace every listed character in the CASE statement in one go?

Thank you!

Solution

In this case the CASE-WHEN construct is unnecessary. You just need a mass REPLACE, which looks like this:

REPLACE(
  REPLACE(
    REPLACE(
      REPLACE([EmpName], 'æ', 'ae'),
    'ø', 'oe'),
  'å', 'aa'),
'-', '')

Unfortunately, the replacement strings have different size than the original. So you can not use TRANSLATE here.