Searching a keyword in a long string in mysql?

Question

Hello

keyword = house

SELECT * FROM products WHERE description LIKE '%house%'

This query also returns records having keyword, for example, courthouse, but I want to look for house only. How do I search anywhere in the description for "house" only?

Thanks.

UPDATE

just for more clarification ..

actually house, can be at 
  -  the start of the description .. "House for sale..", 
  -  can be quoted -- "house", 'house'
  -  can have exclamation .. house!!!, house!
  - others - house? etc .. 

this is why I used %house%

Answer 1

The % before and after will search any matching text before and after the search keyword, try this instead for exact search:

SELECT * FROM products WHERE description = 'house'

Answer 2

Try a space around the keyword:

SELECT * FROM products WHERE description LIKE '% house %' OR description LIKE 'house %' OR description LIKE '% house' OR description LIKE '% house%'

Edit: added more options in search. The last one would allow for house. or house,, though something like housecat would also match. Regex would work best here. This solution came from Astandar, who deleted his answer.

Answer 3

Are you looking for longs strings containing the whole word?

http://stackoverflow.com/questions/656951/search-for-whole-word-match-in-sql

Answer 4

SELECT * FROM products WHERE description rlike '[[:<:]]house[[:>:]]'

rlike is synonim for REGEXP.

[[:<:]] denotes the start of the word and the
[[:>:]] end of the word.

It works for all your requirements (case insensitive, with quoted words or words ending, or begging, with exclamation points and other non-letter characters)

Answer 5

Simple thing only house means, use this

   SELECT * FROM products WHERE description = 'house'

If you want any word contain house in backside means use this,

   SELECT * FROM products WHERE description LIKE '%house'

If you want any word contain house in frontside means use this,

   SELECT * FROM products WHERE description LIKE 'house%'

If you want any word contain house in anywhere in description means use this,

   SELECT * FROM products WHERE description LIKE '%house%'