Replace duplicate spaces with single space in TSQL

Question

I need to ensure that a given field does not have more than one space (not concerned about all white space, just space) between characters.

So

'single    spaces   only'

Needs to turn into

'single spaces only'

The below will not work

select replace('single    spaces   only','  ',' ')

as it would result in

'single  spaces  only'

I would really prefer to stick with native TSQL rather than a CLR based solution.

Thoughts?

Answer 1

Even tidier:

select string = replace(replace(replace(' select   single       spaces',' ','<>'),'><',''),'<>',' ')

Output: select single spaces

Answer 2

This would work:

declare @test varchar(100)
set @test = 'this   is  a    test'

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

select @test

Answer 3

see the answers to a similar/identical question: sql-method-to-replace-repeating-blanks-with-single-blanks

Answer 4

This is somewhat brute force, but will work

CREATE FUNCTION stripDoubleSpaces(@prmSource varchar(max)) Returns varchar(max)
AS 
BEGIN
    WHILE (PATINDEX('%  %', @prmSource)>0)
     BEGIN
        SET @prmSource = replace(@prmSource  ,'  ',' ')
     END

    RETURN @prmSource
END

GO

-- Unit test -- 
PRINT dbo.stripDoubleSpaces('single    spaces   only')

single spaces only

Answer 5

If you know there won't be more than a certain number of spaces in a row, you could just nest the replace:

replace(replace(replace(replace(myText,'  ',' '),'  ',' '),'  ',' '),'  ',' ')

4 replaces should fix up to 16 consecutive spaces (16, then 8, then 4, then 2, then 1)

If it could be significantly longer, then you'd have to do something like an in-line function:

CREATE FUNCTION strip_spaces(@str varchar(8000))
RETURNS varchar(8000) AS
BEGIN 
    WHILE CHARINDEX('  ', @str) > 0 
        SET @str = REPLACE(@str, '  ', ' ')

    RETURN @str
END

Then just do

SELECT dbo.strip_spaces(myText) FROM myTable