SQL Server: How to Remove Extra Spaces From String Value

Extra spaces between characters of a string value is a common problem and if you’re a developer then you must have faced the problem. On request of a blog reader here is a script from my query bank which I like to use to remove such extra spaces.

--Create a temp table for testing our query

CREATE TABLE #ExtraSpaces ( MyVal VARCHAR(8000))

--Insert some value to test

INSERT  INTO #ExtraSpaces

SELECT  'This     is my                         message.               '

UNION ALL

SELECT 'This      message   contains            tabs and    extra       spaces'

-- Lets remove extra spaces and tabs

WHILE 1 = 1

    BEGIN

        UPDATE  #ExtraSpaces

        SET    MyVal = REPLACE(

SUBSTRING(MyVal, 1,

CHARINDEX('  ', MyVal, 1) - 1) + ' '

                + LTRIM(

SUBSTRING(MyVal,

CHARINDEX('  ', MyVal, 1), 8000)),'  ',' ')

        WHERE   CHARINDEX('  ', MyVal, 1) > 0

        IF @@rowcount = 0

            BREAK

    END

--Lets see the updated result

SELECT  MyVal FROM    #ExtraSpaces

--drop temp table when not required

DROP TABLE #ExtraSpaces