Remove Extra Space from Text in Sql

select name,
replace(replace(replace(name,' ','<>'),'><',''),'<>',' ')
from qtemp/mydata


NAME REPLACE
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith

So how does it work? The innermost REPLACE changes all blanks to a less-than greater-than pair. So, if there are three spaces between Joe and Smith, the innermost REPLACE returns Joe<><><>Smith.

The middle REPLACE changes all greater-than less-than pairs to the empty string, which removes them. Joe<><><>Smith becomes Joe<>Smith.

The outer REPLACE changes all less-than greater-than pairs to a single blank. Joe<>Smith becomes Joe Smith. Clever!

You do not have to use the less-than and greater-than symbols. Any two characters that are not used in the field will work.