Mysql – SQL MATCH AGAINST incomplete string with space

MySQLpattern matching

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.

Best Answer

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.

Related Solutions

Mysql – how to fetch different data on compare rows of two tables

Here is your original query

SELECT ua.package_name, c.id as cid, c.title, c.description, c.iconlink, c.marketlink
FROM `user_app` AS ua INNER JOIN `creative` AS c
ON ua.package_name <> c.package_name
where ua.imei_num = '30c899b5c0f29dbc95418c4173f6f745'
and c.package_name in (12,13)
group by `package_name`

I would like to suggest refactoring the query to retrieve needed rows before doing the JOIN

SELECT ua.package_name, c.id as cid, c.title, c.description, c.iconlink, c.marketlink
FROM
(
    SELECT package_name FROM `user_app`
    WHERE imei_num = '30c899b5c0f29dbc95418c4173f6f745'
) AS ua INNER JOIN
(
    SELECT * FROM `creative`
    WHERE c.package_name in (12,13)
) AS c
ON ua.package_name <> c.package_name
group by ua.package_name;

Give it a Try !!!

Thesql column naming conventions for foreign keys

I prefer user_id but I think it doesn't really matter as long as you stay consistent and not mix them up so you keep your overview structured and readable.

Best Answer

Related Solutions

Mysql – how to fetch different data on compare rows of two tables

Thesql column naming conventions for foreign keys

Related Question