SQL MATCH AGAINST incomplete string with space

Question

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.

Answer 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.