HiCntry HiCntry - 5 months ago 9
MySQL Question

SQL syntax error in WHERE CONTAIN query

I've got full-text indexing/search setup and I'm trying to pull any row where the 'LotteryChoices' column contains any of the query parameters. I keep getting an "Incorrect syntax near 'OR' " error. I've read through a bunch similar threads but they seem to have conflicting answers on the correct syntax for a WHERE CONTAINS query. Below is the query. Thanks in advance for any help, my SQL Fu is weak.

SELECT * FROM Lotto WHERE CONTAINS (LotteryChoices, 'Markley' OR 'Lindley' OR 'Tagert' OR 'Green' OR 'Goodwin' OR 'Wilson' OR 'Greene' OR 'Barnard' OR 'Opa' OR 'Taylor'

jpw jpw
Answer

Since you are referring to full-text indexing and the CONTAINS function is used in T-SQL/SQL Server I'm guessing you might not be using MySQL but rather MS SQL and if so the query should be:

SELECT * 
FROM Lotto 
WHERE CONTAINS (LotteryChoices, 'Markley OR Lindley OR Tagert OR Green OR Goodwin OR Wilson OR Greene OR Barnard OR Opa OR Taylor'

That is, with the OR as part of the contains_search_condition as per the documentation.

(If you are indeed using MySQL I'll remove my answer)