Replace 2 spaces with 1

Question

I want to replace all the values within a column that are:

 Boston, MA (2 space)

with:

 Boston, MA (1 space)

I have gotten as far as:

select [Location], replace(replace(replace([Location],' ','<>'),'><',''),'<>',' ') from [Table]

How can I replace the values in the [location] column with the result of the query above?

note: there are other values in the [location] column, eg.

Hong Kong London, UK

Take a look at this article. sqlservercentral.com/articles/T-SQL/68378 It explains how to replace multiple spaces with a single space. If you only have 2 spaces then the fine code that M.Ali posted will work great. If however, you find that you have multiple spaces the article I linked shows a great way to deal with that. — Sean Lange
– Sean Lange, Commented Sep 2, 2014 at 21:34

M.Ali · Accepted Answer · 2014-09-02 20:31:46Z

select [Location], replace([Location],' ', ' ') from [Table]

to update the column

UPDATE [Table] SET [Location] = replace([Location],' ', ' ')

1 Answer 1