Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

WHERE Like vs. equijoin 1

Status
Not open for further replies.

STirone

Technical User
May 23, 2001
6
US
Does "WHERE Like", when used on a field name, return an exact match like an equijoin, or does it return similar data as if a wildcard is used?

Example: I have to compare the contents of two fields, each in its own two table, and present consolidated results. Let's call them A.COMPANY and B.COMPANY. Both fields contain overlapping data that represent the same real-world companies.

An equijoin pulls up a number of exact matches. But I want to see if there are companies that match but have slightly different spellings, an additional comma or period, etc.: e.g., "USA Company Inc" vs. "USA Company, Inc." I use "WHERE Like [A].[COMPANY]" I get back the same results as the equijoin. Trying wildcards doesn't work with a field name. At least, I can't figure out how to use them.

Is the "WHERE Like" acting like an equijoin and ignoring near matches? Or is the data in both fields and tables exactly match? (Not impossible, but unlikely.)

If "WHERE Like" acts like an equijoin, how can I find near matches on two fields?

Thanks!
 
You have used "WHERE Like" consistently, including in your example code, so I'm assuming you have actually coded just like that. But "WHERE Like [A].[Company]" is invalid syntax, and "WHERE Like" isn't some special kind of syntax.

"Like" is just a plain (though powerful) operator keyword. In an expression you use it as: "string-expr1 Like string-expr2". Usually, string-expr2 contains one or more wildcard characters ("*" and "?" are the most common).

"Like" isn't going to be very convenient for you in this case. You'd have to tell it, using wildcards, where you expected the extra punctuation to be, which is highly variable in your case.

A better approach would be to write a function that removes all the punctuation from its argument and returns the resulting "cleaned" string. Then you could call it from your WHERE clause expression. Something like this:
WHERE Depunctuate([A].[Company]) = Depunctuate([ignore].[Company][/ignore])
Rick Sprague
 
Thanks very much! I guess I wasn't quite clear. The strict SQL format would be "WHERE ((([A].[COMPANY) Like .[COMPANY]));" I was just using shorthand, I guess.

Using depunctuate, however, will only get me half-way there. I'm also facing "USA Company" and "USA Co". I need a way to compare those fields for near matches.
 
Thanks very much! I guess I wasn't quite clear. The strict SQL format would be "WHERE ((([A].[COMPANY) Like [B ].[COMPANY]));" I was just using shorthand, I guess. Didn't know about depunctuate! Great tip!

Using depunctuate, however, will only get me half-way there. I'm also facing "USA Company" and "USA Co" in addition to "USA Company, Inc." and "USA Company Inc". Same real-world entity, and almost the same data, just not an exact match. I need a way to compare those fields for near matches.
 
I'm afraid there just isn't any "kinda sorta matches" operator in Access (or any other language I'm familiar with, for that matter). :)

To answer your original question, the Like operator accepts a simple regular expression syntax, so no, it's not necessarily like an equijoin. But YOU have to provide the wildcards to indicate where you'll accept variability.

Here's one thing that might do a fairly decent job of it. Instead of Depunctuate(), create a GenRegExp() function that replaces all strings of one or more punctuation characters and/or spaces with an asterisk, and also appends an asterisk to the end of its argument. Then you could use this restriction:
[ignore]WHERE [A].[Company] LIKE GenRegExp(.[Company])[/ignore]
Rick Sprague
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top