using the LIKE operator with wildcards to match names
I am using SQL in ColdFusion 9 to match names in an MS Access 2003 database table.
The Visitors field of MyTable contains the name "Smith" in six records.
1. Smith Jones Wilson
2. Smith, Jones, Wilson
3. Smith(Jr.), Jones, Wilson
4. Jones Wilson Smith
5. Wilson Smith Jones
6. Smith
7. Smithson, Jones, Wilson
8. Jones, Wilson, Arrowsmith
To find out which records contain the name Smith (but not Smithson or Arrowsmith) I now need to write four "OR" lines:
WHERE MyField LIKE 'Smith[!a-zA-Z]%' (finds only the Smiths in records 1, 2 and 3)
OR MyField LIKE '%[!a-zA-Z]Smith' (finds only the Smith in record 4)
OR MyField LIKE '%[!a-zA-Z]Smith[!a-zA-Z]%' (finds only the Smith in record 5)
OR MyField = 'Smith' (finds only the Smith in record 6)
I would like to know how to combine these four lines into one which would find all six records containing Smith.
Thank you for your help.
