MS SQL CONTAINSTABLE() - Must Contain Both Words

skij
skij used Ask the Experts™
on
This does not return any results even though both "red" and "yellow" are found because the exact phrase "red and yellow" is not found:

INNER JOIN CONTAINSTABLE(ipm_asset, *, 'FORMSOF(THESAURUS, "red AND yellow")')

Open in new window


How can I use CONTAINSTABLE so that both the words "red" and "yellow" are found in any order or position?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Maybe?:

'FORMSOF(THESAURUS, "red" AND "yellow")')

I believe because of the double quotes surrounding the whole text, SQL "thinks" you are looking for the string 'red AND yellow', rather than 'red' and 'yellow'.

Author

Commented:
@Scott Pletcher: your idea returns a syntax error.

I need a solution that actually works.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Did you complete the JOIN syntax??  You left the JOIN "hanging" -- no table alias for the CONTAINSTABLE and no ON clause for the JOIN -- on your initial post so I didn't know how to complete it, but it cause a syntax error if left hanging.
Commented:
I  figured it out, based on your comment.  Each term needs its own FORMSOF() statement.

This works:

INNER JOIN CONTAINSTABLE(ipm_asset, Search_Tags_Speedy, 'FORMSOF(INFLECTIONAL, "foods") AND FORMSOF(INFLECTIONAL, "view") AND FORMSOF(INFLECTIONAL, "whole")') as ipmrank  ON a.asset_id = ipmrank.[KEY]

Thanks.

Author

Commented:
Each term needs its own FORMSOF() statement.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial