Unfortunately I cannot provide the actual data but you are right - I was pretty vague
Here's a simple sample of what I'd like to do.
LID, iValue, iLookupValue
1 FOOTBALL spotkick
2 FOOTBALL shootout
3 RUGBY dropgoal
1 Farrell kicks winning dropgoal in thrilling final
2 Rooney double with spotkick downs Liverpool
3 Thrilling penalty shootout in Final
The idea is to search within the SportArticle column for keywords in the lookup table and then associate each article with a sport...
I've used LIKE but the problem with that is that it can return any number of matches for simpler words such as (non sport example here: war can match wars, warning, etc)...CONTAINS provides a lot more in INFLECTIONAL, THESAURUS and NEAR that give me options on matching criteria and also...more importantly ranking of matches...
select * from lookup, maintable where CONTAINS(sportArticle, iLookupValue) > 0 just fails completely since I have read you can't use column names in the CONTAINS...so in order to use CONTAINS I've have to think about a cursor loop through the lookuptable on the maintable and that just seems super-inefficient...but...if it's the only way...
REGEX has crossed my mind but I'd rather focus on the core functionality that SS provides first than try to build my own matcher.
Does that make a bit more sense ?