SQL method to replace repeating blanks with single blanks

Link : http://stackoverflow.com/questions/2182877/sql-method-to-replace-repeating-blanks-with-single-blanks

down voteaccepted

Here is a simple set based way that will collapse multiple spaces into a single space by applying three replaces. DECLARE @myTable TABLE (myTextColumn VARCHAR(50)) INSERT INTO @myTable VALUES ('0Space') INSERT INTO @myTable VALUES (' 1 Spaces 1 Spaces. ') INSERT INTO @myTable VALUES (' 2 Spaces 2 Spaces. ') INSERT INTO @myTable VALUES (' 3 Spaces 3 Spaces. ') INSERT INTO @myTable VALUES (' 4 Spaces 4 Spaces. ') INSERT INTO @myTable VALUES (' 5 Spaces 5 Spaces. ') INSERT INTO @myTable VALUES (' 6 Spaces 6 Spaces. ') select replace( replace( replace( LTrim(RTrim(myTextColumn)), --Trim the field ' ',' |'), --Mark double spaces '| ',''), --Delete double spaces offset by 1 '|','') --Tidy up AS SingleSpaceTextColumn from @myTable Your Update statement can now be set based: update @myTable set myTextColumn = replace( replace( replace( LTrim(RTrim(myTextColumn)), ' ',' |'), '| ',''), '|','') Use an appropriate Where clause to limit the Update to only the rows that have you need to update or maybe have double spaces. e.g. where 1<=Patindex('% %', myTextColumn) I have found an external write up on this method: REPLACE Multiple Spaces with One