>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.               ‘
SELECT ‘This      message   contains            tabs and    extra       spaces’
— Lets remove extra spaces and tabs
WHILE 1 = 1
        UPDATE  #ExtraSpaces
        SET    MyVal = REPLACE(
CHARINDEX(‘  ‘, MyVal, 1) 1) + ‘ ‘
                + LTRIM(
CHARINDEX(‘  ‘, MyVal, 1), 8000)),‘  ‘,‘ ‘)
        WHERE   CHARINDEX(‘  ‘, MyVal, 1) > 0
        IF @@rowcount = 0
–Lets see the updated result
SELECT  MyVal FROM    #ExtraSpaces
–drop temp table when not required
DROP TABLE #ExtraSpaces

Posted on May 19, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 2 Comments.

  1. >How about this:WHILE EXISTS (SELECT TOP 1 MyVal FROM #ExtraSpaces WHERE CHARINDEX(' ', MyVal, 1) > 0)BEGIN UPDATE #ExtraSpaces SET MyVal = REPLACE(MyVal, ' ', ' ')END

  2. WHILE EXISTS (SELECT TOP 1 MyVal FROM #ExtraSpaces WHERE CHARINDEX(' ', MyVal, 1) > 0)BEGINUPDATE #ExtraSpacesSET MyVal = REPLACE(MyVal, ' ', ' ')ENDthis script will take time to execute..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: