SQL Replace multiple different characters in string

You just need to daisy-chain them:

REPLACE(REPLACE(T2.[ShipToCode], '&', 'and'), ',', '')

If you use SQL Server 2017 or 2019 you can use the TRANSLATE function.

TRANSLATE(ShipToCode, '|+,-', '____')

In this example de pipe, plus, comma en minus are all replaced by an underscore. You can change every character with its own one. So in the next example the plus and minus are replaced by a hash.

TRANSLATE(ShipToCode, '|+,-', '_#_#')

Just make sure the number of characters is the same in both groups.


We used a function to do something similar that looped through the string, though this was mostly to remove characters that were not in the "@ValidCharacters" string. That was useful for removing anything that we didn't want - usually non-alphanumeric characters, though I think we also had space, quote, single quote and a handful of others in that string. It was really used to remove the non-printing characters that tended to sneak in at times so may not be perfect for your case, but may give you some ideas.

CREATE FUNCTION [dbo].[ufn_RemoveInvalidCharacters]
 (@str VARCHAR(8000), @ValidCharacters VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
  WHILE PATINDEX('%[^' + @ValidCharacters + ']%',@str) > 0
   SET @str=REPLACE(@str, SUBSTRING(@str ,PATINDEX('%[^' + @ValidCharacters +
']%',@str), 1) ,'')
  RETURN @str
END

One comment mentions "dozens of replace calls"... if removing dozens of single characters, you could also use Translate and a single Replace.

REPLACE(TRANSLATE(T2.[ShipToCode], '[];'',$@', '#######'), '#', '')