SQL Replace multiple different characters in string

Question

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')

Which converts & to and, but how do you put multiple values in one line?

Magnetron

Answer 1

You just need to daisy-chain them:

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

Garlan

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

Unaccustomed

However, TRANSLATE is only available from SQL Server 2017 onwards. – Kindred

Why not TRANSLATE into blanks directly ? – Nonmetal

@Ludovic because SQL spits this error if you try "The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters." – Autopilot

Is there a performance gain/loss depending how many times you use replace on a single line, or a single replace and translate? – Marty

Answer 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

Leakey

Preferring this solution since it doesn't daisy-chain dozens of REPLACE calls – Pentothal

Answer 4

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

Threlkeld

Answer 5

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

Ewen

Looks like this function is only available for Oracle, and not SQL Server – Gasket

This function is available in Google BigQuery and PostgreSQL – Ewen

Answer 6

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

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

Paries

Answer 7

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

Thermaesthesia