Creating a SQL query for searching an exact phrase in a given field(s) in a table is not much harder than the simple query for searching a substring. However this query could be very slow if you are searching through huge amount of text in the database. So, a good solution to this problem is first to eliminate the rows which do not contain the phrase as a substring (which is fast) and then perform the regular expression (REGEXP) only to the others. Here is what I'm talking about:
For those of you who have no previous experience with REGEXP in SQL, here are some examples and explanations:
The '[[:<:]]' I used above is used to specify the start of a word and '[[:>:]]' is for the end of a word. As you've seen they are useful to match an exact phrase.
This will match "Connecting to this host" but it won't match "I'm looking for a hosting":
To match only letters and digits, use '[[:alnum:]]'. If you want to allow only the letters from 'e' to 'w' use something like that:
(or)
... REGEXP '[^a-dx-zA-DX-Z]'
... and to match the count of these letters:
... REGEXP '[e-wE-W]{3,}' (more than 3 times)
... REGEXP '[e-wE-W]{2}' (exactly 2 times)
... REGEXP '(host){2}' (exactly 2 times 'host': 'hosthost')
If there are multiple choices, group them with () and separate them using | :
When you are using characters like ?, +, $, ^, don't forget to escape them using the \ because they are special characters:
Details about the regular expressions in MySQL are available in the official reference. This table is also very useful to understand the basics of the REGEXP in MySQL.
