Replacing multiple characters in a CASE statement, SQL

Question

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!

Are you using MySQL, Oracle or MS SQL Server? Don't tag products not involved. — jarlh, Oct 11 '17 at 09:43
I can use any of them that's why I did it :) It worked btw! Thank you so much!!!!! — gripep, Oct 11 '17 at 09:49
Possible duplicate of [Can MySQL replace multiple characters?](https://stackoverflow.com/questions/1671040/can-mysql-replace-multiple-characters) — Sebastian Brosch, Oct 11 '17 at 09:52
I removed the incompatible database tags. Tag your questions with the database you are really using. — Gordon Linoff, Oct 11 '17 at 10:57

Tamas Rev · Accepted Answer · 2018-04-23T12:21:04.783

3

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.

edited Apr 23 '18 at 12:21

answered Oct 11 '17 at 09:50

Tamas Rev

7,008
5
32
49

Replacing multiple characters in a CASE statement, SQL

1 Answers1