whitespace query mystery

Question

I am trying to identify ‘mismatches’ between a table of valid email addresses, and an employees table with an email address field. I’ve just been using the standard ‘find unmatched query wizard’ in Microsoft Access which has identified several problematic records that need resolving. This is not a relational database behind a front end application, just 2 extracts of data imported into Access for cross-referencing.

Most of the mismatches seem to be due to whitespace related issues, e.g. leading or trailing whitespace – however, whilst the spaces are apparent visually when I click in the field from the ‘find unmatched query wizard’ results, I am struggling to pick them up via a SELECT query, so I am now wondering if they are technically considered something different to blank spaces (tabs?).

I am using:

SELECT MyTable.Email_Address

FROM MyTable

WHERE MyTable.Email_Address LIKE ' %' OR MyTable.Email_Address LIKE '% '

And for whatever reason, the same mismatches are not being returned in the results (I’ve even tried double/treble spaces). In case it may be influencing anything, MyTable.Email_Address is the data type Short Text. Are there any other clever techniques that can identify what exactly Access considers these leading/trailing whitespaces as, or a better query to help find anything whitespace related leading/trailing the address?

Answer 1

Use a criteria of:

Like " "

Answer 2

The first and last character is usually a letter, therefore you can use where Email_Address not like '[a-z]%[a-z]' to include the problematic entries that start or end with various spaces. In case of Access SQL, use “*” instead of “%”.

In Access you can also use Regular Expressions and VBA for more sophisticated filtering.

Answer 3

You can identify a string expression which includes leading or trailing spaces by comparing the length of the expression with the length of the trimmed expression, e.g.

 

x = "ABC"

y = "ABC "

z = " ABC"

? Len(x) = Len(Trim(x))

True

? Len(y) = Len(Trim(y))

False

? Len(z) = Len(Trim(z))

False

 

To identify a string expression which includes one or more spaces at any positions call the Replace function rather than the Trim function, e.g.

 

a = "ABC"

b = "AB C"

? Len(a) = Len(Replace(a," ",""))

True

? Len(b) = Len(Replace(b," ",""))

False  

or call the Instr function:

 

a = "ABC"

b = "AB C"

? Instr(a," ")=0

True

? Instr(b," ")=0

False