sql-servert-sql

Replace duplicate spaces with a single space in T-SQL

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

So

'single    spaces   only'

needs to be turned 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 T-SQL rather than a CLR based solution.

Thoughts?

Solution

  • Even tidier:

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

    Output:

    select single spaces