Recently I’ve been stumped with searching a mysql database for exact words. I’ve tried a number of strategies I found online, but none worked exactly as needed.
The REGEXP didn’t work for me at all (probably because I didn’t really understand it in the first place). The MATCH/AGAINST would work, but only sometimes. The LIKE worked, but it included more words than what I wanted.
The solution I came up with was to create a chain of subqueries. The first uses the LIKE query to gather up all the rows that are close to the word and then does a REGEX on those rows to find exact matches. This solution feels a bit heavy so I only recommend it where necessary.
My solution is as follows:
{syntaxhighlighter brush: sql;fontsize: 100; first-line: 1; }SELECT *
FROM $table
AS allrows
INNER JOIN (
SELECT `id`,`text`
FROM (
SELECT `id`,`text`
FROM $table
WHERE (text LIKE ‘%$word%’)
) AS `likerows`
WHERE (`text` REGEXP(‘^.*$word .*$’))
) AS `regexrows`
ON allrows.id=regexrows.id{/syntaxhighlighter}
Here are my previous attempts that may work for you. I suggest trying them out as they are a bit lighter weight and will certainly help out where they can.
{syntaxhighlighter brush: sql;light: true; fontsize: 100; first-line: 1; }WHERE text REGEXP ‘[[:<:]]($word)[[:>:]]'{/syntaxhighlighter}{syntaxhighlighter brush: sql;light: true; fontsize: 100; first-line: 1; }WHERE MATCH(text) AGAINST(‘$word’) // using mysql fulltext on text{/syntaxhighlighter}
AND
{syntaxhighlighter brush: sql;light: true; fontsize: 100; first-line: 1; }WHERE text LIKE ‘%$word%'{/syntaxhighlighter}
Hope this helps someone. Drop a comment or email me if this doesn’t work for you.