using the LIKE operator with wildcards to match names

[Guest]

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.

[BKBK · Accepted solution]

I think the way you have done it is best. Since the word Smith can occur at the beginning, middle or end of a string, it is difficult to write a single regular expression to cover all cases. Even if you could, it would be a complex formula indeed.

[Adam Cameron.]

As this is a SQL-specific question, you might be better off asking it on a SQL forum?  Does Access even support regexes?

For a CF the regex you'd be after "\bsmith\b".  You should read the Access regex reference to see if it supports this.  A CF regular expression is no use to you here as the comparison needs to be done on the DB,  not in CF.

--

Adam

Misread the question.  Revising.

[Guest]

Both replies above told me just what I need to know. So I will stick with what I had already worked out, since the use of the Regex in Access is apparently not supported in my situation. Thanks for the help!!