Searching MySQL Database for EXACT word

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:

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

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.

WHERE text REGEXP '[[:<:]]($word)[[:>:]]'
WHERE MATCH(text) AGAINST('$word') // using mysql fulltext on text

AND

WHERE text LIKE '%$word%'

Hope this helps someone. Drop a comment or email me if this doesn't work for you.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <p> <pre>
  • Syntax highlight code surrounded by the {syntaxhighlighter SPEC}...{/syntaxhighlighter} tags, where SPEC is a Syntaxhighlighter options string or "class="OPTIONS" title="the title".
  • Lines and paragraphs break automatically.
  • E-Mail addresses are hidden with reCAPTCHA Mailhide.

More information about formatting options

CAPTCHA
Complete this form and then pat yourself on the back.