String in string from table?

[sailorjosh]

I've got a list of customers that includes a field for company names. I want to pull out a subset of companies using a table of "companies of interest" that just has a list of company names. Simple, do a join.

Complication: my "companies of interest" table includes "3M", but some customers type in "3M Corporation" or "The 3M Corporation". I could add variations to my companies of interest list to catch them, but that's (a) inflexible, (b) time consuming, (c) it's virtually impossible to anticipate all the variations, and (d) inelegant. I can do a WHERE xxx = yyy to catch individual cases, but is there a way to see if the Company field in the customers table contains any of the values in my comparison table? Thus SELECT stuff WHERE "does the string in the customer field contain any of the strings listed in my 'companies of interest' table?"


Josh Freedman
Search Engine Marketing

http://www.joshfreedman.com

"More Traffic, More Sales"

 

[Golom]

The exact syntax depends on the constructs available in your DBMS.

SELECT C.Company, C.CompanyName, I.CompanyName

FROM Companies C, COI I

Where InStr(C.CompanyName, I.CompanyName) > 0