When I was concatenating some fields to create an address, I ended up with a number of spaces in my address that were unnecessary. For example, I had this:
SELECT StreetNumber + ' ' + Direction + ' ' + StreetName + ' ' + StreetType as Address
However, when an address didn’t have a direction, I ended up with a double-space in the middle of my address, and I wanted a way to clean it up. Enter the code I found at http://www.itjungle.com/fhg/fhg101106-story02.html:
SELECT name, REPLACE(REPLACE(REPLACE(name,' ','<>'),'><',''),'<>',' ') FROM SomeTable
This shortens any run of spaces in the string into a single space – sneaky! It works in any language that supports a function like REPLACE, which scans one string for instances of a second string, and swaps them out for something else.