Replace Multiple Spaces with One

Once in a while I encounter requirements to remove multiple spaces in a string column. My most common solution is to use loop to replace multiple spaces with one. The problem starts when the value that needs cleaning is a column in huge table. There's a possibility that the operation may result in multiple table write or an RBAR (Row By Agonizing Row).

Browsing around I saw this function. The best part of this function is that it's a set-based operation which, most of the time, is faster. With permission to it's original author, I'm posting it here so I can use it next time I need it.

CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN   replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
  '                                 ',' '),
  '                 ',' '),
  '         ',' '),
  '     ',' '),
  '   ',' '),
  '  ',' '),
  '  ',' ')
END

Big thanks to Michael Mierruth


 ~~ CK