SQL Code Snippets - Replace Arbitrary Spaces

Often there is a need to replace arbitrary spaces in a string in SQL.  Often this is solved using functions, ctes, or other long methods of string replacement.  Oddly, the fastest that I have found is from Try, Catc... Finally it uses a simply set of replacement statements to reduce the number of spaces in a string.

The basic issue is if you have an set of strings:

"This is      a string    with spaces"
"   This is a            string     with spaces"

how do you make them equal to each other by removing arbitrary spaces so that it says

"This is a string with spaces"

I have made some small changes to the code in the mentioned blog, and the code I use is the following:

SELECT REPLACE(REPLACE(REPLACE('This is      a string    with spaces' ,' ',CHAR(7) + CHAR(8) ),CHAR(8) + char(7),''),CHAR(7) + char(8),' ')

The reason I made the change was so that I would be using characters that should never accidentally show up in regular text data.  Or are at least much less prevalent than the less than and greater than character.

This is also another great resource on this problem that explores the many other methods, and even provides the ability to do a speed test on the various methods.

http://improvingsoftware.com/2011/03/21/replacing-multiple-spaces-in-a-string-with-a-single-space-in-sql/