Removing multiple spaces in a string

From Cosmin's Wiki

Home > SQL Server > Removing multiple spaces in a string

I have often faced the situation where I had to work with strings (for example comparison of strings) and I had problems because the some of the strings contained several spaces (2 or more consecutive spaces instead of one). Thus, I needed a way of removing the extra blanks from my string.

Here is how to do it:

declare @mystring varchar(255)
SELECT @mystring='a b  c    d e '
SELECT ltrim(rtrim(REPLACE(REPLACE(REPLACE(@mystring,'  ',' `````'),'````` ',''),'`````','')))

Should you have to do this operation in a repeated way, you could also write a small funtion:

CREATE FUNCTION dbo.fn_removeMultipleSpaces(@input varchar(255))
returns varchar(255)
AS begin
    RETURN ltrim(rtrim(REPLACE(REPLACE(REPLACE(@input ,'  ',' `````'),'````` ',''),'`````','')))
end

and then call this function as:

SELECT dbo.fn_removeMultipleSpaces('a b  c    d e ')