'SQL Replace multiple different characters in string

I need to replace multiple characters in a string. The result can't contain any '&' or any commas.

I currently have: 

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

But how do you put multiple values in?

Many thanks!

sqlreplacesql-server-2016


Solution 1:[1]

You just need to daisy-chain them:

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

Solution 2:[2]

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], '[];'',$@', '#######'), '#', '')

Solution 3:[3]

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

Solution 4:[4]

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.

Solution 5:[5]

If you need fine control, it helps to indent-format the REPLACE() nesting for readability.

SELECT Title,
REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(RTRIM(Title),
                            ' & ',''),
                        '++', ''),
                    '/', '-'),
                '(',''),
            ')',''),
        '.',''),
    ',',''),
' ', '-')
AS Title_SEO
FROM TitleTable

Solution 6:[6]

Hope this might helps to anyone

If you want to replace multiple words or characters from a string with a blank string (i.e. wanted to remove characters), use regexp_replace() instead of multiple replace() clauses.

SELECT REGEXP_REPLACE("Hello world!123SQL$@#$", "[^\w+ ]", "")

The above query will return Hello world123SQL

The same process will be applied if you want to remove multiple words from the string.

If you want to remove Hello and World from the string Hello World SQL, then you can use this query.

SELECT REGEXP_REPLACE("Hello World SQL", "(Hello|World)", "")

This will return SQL

With this process, the query will not look redundant and you didn't have to take care of multiple replace() clauses.

Conclusion

If you wanted to replace the words with blank string, go with REGEXP_REPLACE().

If you want to replace the words with other words, for example replacing & with and then use replace(). If there are multiple words to be replaced, use multiple nested replace().

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Siyual
Solution 2 CLaFarge
Solution 3 Peter Schott
Solution 4 Mark Iannucci
Solution 5 Montet
Solution 6 SHIVAM SINGH

Related Questions