Jeff, you've come up with a nice, simple method for doing this. It made me go back and look at how I had coded it in my application. My code seems to avoid RBAR, and also deals with other "space" characters (tabs and cr/lf) [something that could be easily added to your method, of course.] But my code is more convulted and probably less efficient due to the creation of a temp table to split the words into:
CREATE procedure [dbo].[removeExtraWhiteSpace]
@test-2 varchar(max) output
SET NOCOUNT ON
set @test-2 = replace(@test,char(9),' ')
set @test-2 = replace(@test,char(10),' ')
set @test-2 = replace(@test,char(13),' ')
set @test-2 = replace(@test,'''', '''''')
CREATE TABLE #words(seq int identity, singlePhrase varchar(8000))
-- Split by words and load text into the table
SELECT @test-2 = 'INSERT INTO #words(singlePhrase) SELECT A=''' +
REPLACE(@test, ' ', ''' UNION ALL SELECT ''') + ''''
set @test-2 = '';
select @test-2 = @test-2 + singlePhrase + ' ' FROM #words where ltrim(singlePhrase) > '' order by seq;
drop table #words
I think I'll switch over to your method.