How to make mysql LIKE search for exactly single words only

Question

I have a query like this:

SELECT * FROM mytable where description like %STRING%

The problem is: When I search for JAVAit returns me even the records with JAVAscript.
But, JAVA != JavaScript, right ? How can I work around it ?

Answer 1

MySQL's LIKE operator isn't really suitable to detect an exact single word inside a string. But REGEXP, which supports regular expressions, can handle this. Consider the following query:

SELECT * FROM mytable WHERE description REGEXP '[[:<:]]Java[[:>:]]';

This corresponds to matching the pattern \bJava\b, i.e. the word Java by itself.

Demo

Edit:

If you are trying to execute this query using Laravel, then whereRaw should come in handy:

$results = DB::table('mytable')
            ->whereRaw('description REGEXP ?', ['[[:<:]]Java[[:>:]]'])
            ->get();