Replacing multiple spaces in a string using T-SQL

December 17, 2008 8:47 AM

Please help me find the T-SQL that removes duplicate spaces within a string (or suggest your own method).

I swear I found a few months ago a T-SQL snippet that removes duplicate spaces within a string. For example:

1 2  3   4    5     6

becomes

1 2 3 4 5 6

The solution I thought I saw used a three or four nested replace calls, which replaced some of the spaces with a ^ and eventually replaced the ^ with an empty string.
I know I could do something like

while charindex(' ', @string) > 0
begin
   set @string = replace(@string, '  ', ' ')
end

but the replace method was pretty clever (and maybe faster?). I swear I saw it on AskMe, but I can't find it and my Google searches fail. Anyone know what I'm talking about, or know of a faster, better, cheaper method? Thanks!

posted by DakotaPaul to computers & internet (2 comments total)

maybe it was this question? http://ask.metafilter.com/97942/Translating-s-g-into-TSQL
posted by jepler at 9:27 AM on December 17, 2008

Bingo! Thanks, jepler!
posted by DakotaPaul at 9:40 AM on December 17, 2008