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.

4
  • 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? Dec 6, 2015 at 3:03
  • Yes, sorry I didn't know what you meant. I'm still new to this.
    – user82066
    Dec 6, 2015 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.. Dec 6, 2015 at 3:22
  • I updated my answer based on your EDIT. Dec 6, 2015 at 4:35

1 Answer 1

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 and acknowledge that you have read and understand our privacy policy and code of conduct.