The Problem

Double spaces in strings is a real pain. For those who can’t picture it see the example below…

A particular column in a row is declared as some kind of string (char etc) and the data one wants to store is something like:

'John Smith'

But due to a data entry/import error the following gets stored:

'John  Smith'

…or worse still…

'John    Smith'

This issue can be a real pain, especially when the data’s being used for marketing purposes. Nobody likes to receive postal mailings, especially when it’s addressed to ‘Mr John   Smith’.

The Solution

Firstly, the obvious will not work across all versions of SQL Server (notably 2000)…

select * from table where string like '%  %'

SQL 2000 and maybe 2003 will return every row with a single and double space. So instead we have to use the following…

select * from table where string <> replace(replace(replace(string,' ','<>'),'><',''),'<>',' ')

That will list all the rows with double spaces in column ‘string’ in table ‘table’. So to update the string and remove those bloomin double spaces run the following query…

update table set string = replace(replace(replace(string,' ','<>'),'><',''),'<>',' ')

Bingo!