2

I have MySQL database of places, and I want to use the MATCH (...) AGAINST (...) function to find places with two-part names, such as New York. This is a problem because the match against syntax will recognize the second part of the name as an 'OR' condition.

SELECT * FROM places WHERE MATCH (...) AGAINST ('New York')

Will match against columns containing the word "New" or the word "York".

How can I specify the place as one string to match against, or is there a way to escape spaces in the query?

If there is no solution, then I accept workarounds to the problem if I only get results from my database that contain the two-part name.

EDIT: I'm using this for a program where a user types and I query my database for suggestions to predict what the user could be typing, so I need to match against a string that is probably incomplete.

  • Please update the question to specify exactly with RDBMS. I know it's not Microsoft SQL Server ;-). UPDATE: a quick Google search seems to indicate that this is MySQL, correct? – Solomon Rutzky Dec 6 '15 at 3:03
  • Yes, sorry I didn't know what you meant. I'm still new to this. – user82066 Dec 6 '15 at 3:17
  • Ok. I found something, but it always helps to know what version of the RDBMS one is on as sometimes features change across versions.. – Solomon Rutzky Dec 6 '15 at 3:22
  • I updated my answer based on your EDIT. – Solomon Rutzky Dec 6 '15 at 4:35
1

If you want to use Full-Text Search, then in order to get an exact phrase match it would seem that doing a Boolean Full-Text Search would be the way to go, enclosing the term New York in double-quotes, hence:

SELECT * FROM places WHERE MATCH (field) AGAINST ('"New York"' IN BOOLEAN MODE);

But, if you are looking to do a "contains" type of search where the user-entered value is a literal fragment (i.e. what they entered needs to match as-is, but can also be a part of a larger string and is not necessarily the entire match), then perhaps doing a regular LIKE '%{user_input}%' would be best.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy