Ignoring whitespace

  • Thread starter Thread starter Davie
  • Start date Start date
D

Davie

I have a table with postcodes in it, how can i search for a postcode and
ignore whitespace??

For example some records may have :

AB12 3BH

AB123BH

How can i search in Access? This is for the purposes of joining tables on
postcode values.
 
Davie said:
I have a table with postcodes in it, how can i search for a postcode
and ignore whitespace??

For example some records may have :

AB12 3BH

AB123BH

How can i search in Access? This is for the purposes of joining
tables on postcode values.
Click to expand...

You would have to join on an expression that used the Replace() function on both
sides of the relationship to strip out the space character. This would be
terribly inefficient, but might be acceptable if the tables are small. It would
have to be written in SQL view (or at least modified after creating in the
designer) and would look like...

SELECT Field1, Field2, etc..
FROM TableA JOIN TableB
ON Replace(TableA.PostCode, " ", "") = Replace(TableB.PostCode, " ", "")

It would be better to run update queries one time to remove all the white space
so you could use a standard join with indexes on both fields.
 
As Rick mentions, you should standardize to a specific format, especially if
you are using it as a type of index. The spaces in the postcode are
unnecessary and should be removed. Anywhere that a postcode can be entered
should also be changed to remove the blanks.

In the case of names like "highstreet" and "high street" the blanks are
significant and should not be removed. You may want to add an extra field
that has a "cleaned up" version of your field (no white space, all upper or
lower case, etc).

John... Visio MVP
 
Back
Top