search table columns for a word in Mysql database

To search the database table for the exact word.

1) Using LIKE

$query = “SELECT * FROM table_name WHERE column LIKE ‘%key_word%’ “;

The above query searches for the string which contains key_word as a single word or mixed in a word.

Example :

if you want to search for a keyword “tea”, above query will fetch records which contain words like “steak”, “teas” ect.

Alternate query is

$query = “SELECT * FROM table_name WHERE column LIKE ‘% key_word %’ “;

using space before and after key_word, which fetches the record  that matches the exact key_word.

there is a little problem with the above query, if the key_word present in the end of the sentence ( total text/string), it wont fetch that record.

Example :

if the string is “this is the test” and if searching for the ‘test’ key_word, the above query will not fetch this record because there is no trailing space after the ‘test’ key_word.

 

2) Using Regular Expression

$query = “SELECT * FROM table_name WHERE column REGEXP ‘[[:<:]]key_word[[:>:]] ‘ “;

The above query will search for the exact word.

In order to search for a column which does not contain a particular word use below query

$query = “SELECT * FROM table_name WHERE column NOT REGEXP ‘[[:<:]]key_word[[:>:]] ‘ “;

The above query will fetch the records which does not contain the key_word in the column.

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *