Monday, July 25, 2011

Searching for a Wildcard in a “LIKE” Criteria

In a query, the LIKE operator allows the user to query for a character or group of characters anywhere within a text field.  It does this by matching the field to a string which mixes the character(s) you want to match with wildcard characters like * and ?. Some examples:

  • FirstName LIKE "C*"  will return any name that starts with a C (i.e. Carlton, Clark)
  • LastName LIKE "*-*"  will return any last name that has a hyphen anywhere in the field (i.e. Flickema-Carlson, Smith-Jones)
  • PONum LIKE "C????"   will return and PO number that starts with a C and has exactly 5 characters. (i.e. CSIDF, C24DG)
  • PONum LIKE "C##"  returns all values that start with a C, is exactly 3 characters long, and characters 2 and 3 MUST be numbers. (i.e. C45, C16)

So how can I search for a wildcard character itself?  I can simply enclose the wildcard character with brackets [].

For instance, if I wanted to find any value that has an asterisk (*) anywhere in it, I could do this:

  • PO LIKE "*[*]*"

If I wanted to find any value that starts with a hash mark(#), I could do this:

  • Check LIKE "[#]*"

If I wanted to find any value ending with a question mark (?), I could do this:

  • Comment LIKE "*[?]"

5 comments:

yrichter said...

Hi Roger,

Is there a way to modify the LIKE operator to obtain a result similar to what CONTAINS provides in SQL Server? LIKE works great when you're searching for a set criteria or even wildcards but what if you're comparing a field against another field's value and want to obtain any result that contains the other fields value anywhere in the string?

For example Like "*" & [table].[samplefield] & "*" returns samplefield but it would miss [samplefield2]

Thanks,

Isaac Richter

yrichter said...

Is there a way to use LIKE to approximate what you would get with a CONTAINS operator in SQL Server? I'd like to query a column for matches contained in another column (same table or another table, doesn't matter). LIKE is good if I have one criteria to search for, but if I have a column full of values, I can't use wildcards.

I've tried something like:

Like "*" & [Table].[Field] & "*"

But it doesn't match 100%.

Thanks,
Isaac

Roger Carlson said...

Off hand, I don't know of one. That's not to say there isn't. I'd try posting the question on http://www.utteraccess.com.

yrichter said...

Thanks. The only alternative I've been able to come up with so far is to write some code to iterate through the column, issuing individual LIKE queries for each record, but that is so horribly inefficient (searching the entire table for one matching record over and over again) there must be a better way. Thanks, Isaac

sghanna said...

Thanks Roger, good stuff.