optimisteve optimisteve - 29 days ago 10
SQL Question

Identifying records with double space

I am interested in generating a script to identify records under the 'Postcode' column that has double space in between instead of single space.

Table name: Tabu2

Cit_ID DOB Status Postcode
1 12/84 S KT1 3GP
2 11/81 S KT2 2PI
3 13/80 M KT1 2PP
4 15/78 M KT3 2ON
5 13/67 M KT5 4II


Many thanks

Answer
Select *
 From Tabu2
 Where Postcode like '%  %'

Or

Select *
 From Tabu2
 Where CharIndex('  ',Postcode)>0

To fix

Update Tabu2 set Postcode=Replace(Postcode,'  ',' ')
 Where  CharIndex('  ',Postcode)>0