MySQL - Search exact word from string

Question

I want to search exact word from string like

id  Description
1   This is nice pen looking good
2   This is nice pendrive looking good

Search String : pen

My Current query

SELECT * FROM `table` WHERE Description like '%pen%';

Above Query return both record but I want Only first record. Because pen word exact match with my search string.

Expected Output

1   This is nice pen looking good

Demo

Answer 1

Try using regular expressions:

SELECT 
    *
FROM
    `table`
WHERE
    Description regexp '(^|[[:space:]])pen([[:space:]]|$)';

Demo

Or using word boundaries:

SELECT 
    *
FROM
    `table`
WHERE
    Description regexp '[[:<:]]pen[[:>:]]';

Answer 2

You can use REGEXP and the [[:<:]] and [[:>:]] word boundary markers:

SELECT
    *
FROM
    `table`
WHERE
    Description REGEXP '[[:<:]]pen[[:>:]]';

SQL Fiddle Demo

Answer 3

you can use fulltext match like as follow

$query="SELECT * FROM table_name WHERE MATCH (column_name) AGAINST 
  ('search_word' IN NATURAL LANGUAGE MODE)" ;

you can select boolean mode also,it return exact word in search result

Answer 4

Try this to get exact word results

SELECT * FROM `table` WHERE Description ='pen';