SQL Replace multiple different characters in string

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

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

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

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().

In SQL server to avoid daisy-chain them, you can try this:

SELECT REPLACE(TRANSLATE('?File /03-0>6-99 *Test/*.pdf', '<>:"/\|*?', REPLICATE('~',9)),'~','');

In combination with TRANSLATE you can use REPLACE to replace all the character in the string.

TRANSLATE(InputString,CharsToReplace,CharsFromReplace)

Note: CharsFromReplace & CharsToReplace should be of Same Character.

SELECT TRANSLATE('((1))', '()', '##') // Ouput: ##1##

Now we need to Replace hash '##' character with empty string

SELECT REPLACE(TRANSLATE('((1))', '()', '##'),'##',''); // Output: 1

Note: Use it only if there is a lot of Character to be Replaced.

For SqlServer 2014 and older, you can write the equivalent of the TRANSLATE function like this :

CREATE OR ALTER FUNCTION dbo.TranslateBefore2016(@chaine NVARCHAR(MAX), @chars NVARCHAR(MAX), @translations @NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN   
    DECLARE @tmp NVARCHAR(MAX) = @chaine
    
    declare @i as integer = 1
    WHILE @i <= len(@accents)
    BEGIN
        SET @TMP = REPLACE(@TMP COLLATE Latin1_General_CS_AS, substring(@chars , @i, 1) COLLATE Latin1_General_CS_AS, substring(@translations , @i, 1))

        SET @i = @i + 1
    END

    RETURN @TMP
END

Example of usage to remove accents :

select dbo.TranslateBefore2016('ABCDE éeeà', N'ÁÀÂÄÃÅÉÈÊËÍÌÎÏÓÒÔÖÕÚÙÛÜÇáàâäãåéèêëíìîïóòôöõúùûüç', N'AAAAAAEEEEIIIIOOOOOUUUUCaaaaaaeeeeiiiiooooouuuuc')

Please note :

• The usage of the case sensitive collations
• This function will probably need a test to check that @chars and @translations have