Removing Double Spaces from a field ??? 1

[Connatic]

Hi, I have an Address Field in a table, that is a bit of a mess, and I am trying to clean it up.

The first step I would like to do is to remove all the Double spaces for the records in this column, and by this I mean, reducing any double space to a single space!

Joe Bloggs --> would become --> Joe Bloggs

Does any body have any ideas ???

 

[vongrunt]

Repeat this until you get "0 row(s) affected" message:
update mytable set address = replace(address, ' ', ' ')

 

[nigelrivett]

that would need a where clause

select 1
while @@rowcount > 0
update mytable set address = replace(address, ' ', ' ')
where address like '% %'

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.