Sadikhasan
Sadikhasan

Reputation: 18600

MySQL - Search exact word from string

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

Upvotes: 7

Views: 10261

Answers (4)

Bulbul Bigboss
Bulbul Bigboss

Reputation: 11

Try this to get exact word results

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

Upvotes: 1

Ram
Ram

Reputation: 125

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

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

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[[:>:]]';

Upvotes: 13

Faisal
Faisal

Reputation: 4765

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

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

SQL Fiddle Demo

Upvotes: 5

Related Questions