searching fields with spaces in them

[cwizard]

I have a data base of postcodes, stored with spaces in them (LL29 6AF), my users often miss the spaces out.
Can I use somthing like (this does not work!) Select address from postcodes where replace(postcode," ","") = [supplied postcode]
Or is there a simple way of removing the spaces from the postcodes in the table (21,000 records) so I could then remove the spaces in the user supplied postcode before running the select statement.
Nick

 

[JamesLean]

Your replace is correct, you just need to use single quotes:

SELECT * FROM t
WHERE REPLACE(postcode, ' ', '') = 'AB123CD'

You can use the same function to actually update the values already in the table:

UPDATE t
SET postcode = REPLACE(postcode, ' ', '')

--James

 

[cwizard]

James, thank you! isn't it easy when you knoe how.

Nick