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?

score 64 · Accepted Answer · answered Aug 30 '16 at 15:07

64

You just need to daisy-chain them:

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

answered Aug 30 '16 at 15:07

Siyual

16,415
8
44
58

score 39 · Answer 2 · answered Apr 29 '19 at 15:39

39

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

answered Apr 29 '19 at 15:39

CLaFarge

1,277
11
16

4

However, TRANSLATE is only available from SQL Server 2017 onwards. – Simon Wray Jul 02 '19 at 11:17
3

Why not TRANSLATE into blanks directly ? – Ludovic Aubert Nov 20 '19 at 10:40
1

@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." – Nick Allan May 14 '20 at 10:04
Is there a performance gain/loss depending how many times you use replace on a single line, or a single replace and translate? – T.S Oct 06 '20 at 13:50

score 11 · Answer 3 · answered Aug 31 '16 at 10:42

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

Preferring this solution since it doesn't daisy-chain dozens of `REPLACE` calls — Marco Rebsamen, May 23 '18 at 08:28

score 10 · Answer 4 · answered Jul 26 '21 at 15:00

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

score 9 · Answer 5 · edited Jun 30 '23 at 12:53

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

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

In this example the pipe, plus, comma, and 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.

score 3 · Answer 6 · answered Apr 05 '22 at 07:17

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

Looks like this function is only available for Oracle, and not SQL Server — plasmaTonic, Jun 23 '22 at 19:44
This function is available in Google BigQuery and PostgreSQL — SHIVAM SINGH, Jun 24 '22 at 12:35

score 0 · Answer 7 · answered May 19 '23 at 14:56

0

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

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

answered May 19 '23 at 14:56

user1282441

77
1
8

score 0 · Answer 8 · answered Jul 27 '23 at 21:30

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

SQL Replace multiple different characters in string

8 Answers8

Linked