Skip to main content
October 22, 2012
Answered

using the LIKE operator with wildcards to match names

  • October 22, 2012
  • 3 replies
  • 1210 views

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.




This topic has been closed for replies.
Correct answer BKBK

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.

3 replies

October 23, 2012

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!!

Inspiring
October 23, 2012

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.

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
October 23, 2012

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.