Searching a keyword in a long string in mysql?

Question

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%

possible duplicate of http://stackoverflow.com/questions/656951/search-for-whole-word-match-in-sql — Samir Talwar, Apr 20 '10 at 17:36

score 2 · Answer 1 · edited May 23 '17 at 12:19

2

Are you looking for longs strings containing the whole word?

Search for "whole word match" in MySQL

edited May 23 '17 at 12:19

Community

1
1

answered Apr 20 '10 at 17:18

Matt Ball

354,903
100
647
710

1

I think this is the solution, I posted explicit one before checking yours – Unreason Apr 20 '10 at 17:24

score 1 · Answer 2 · answered Apr 20 '10 at 17:15

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'

answered Apr 20 '10 at 17:15

Sarfraz

377,238
77
533
578

@astander: this is what he says: "This query also returns records having keyword, for example, courthouse, but I want to look for house only." – Sarfraz Apr 20 '10 at 17:17
I dont think this is what the OP wants. *How do I search **anywhere in the description** for "house" only* – Adriaan Stander Apr 20 '10 at 17:17
1

@astander: yes his question is not that clear. Let's see what exactly is he looking for :) – Sarfraz Apr 20 '10 at 17:20

Unreason · Accepted Answer · 2010-04-20T17:54:37.830

1

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)

edited Apr 20 '10 at 17:54

answered Apr 20 '10 at 17:22

Unreason

12,556
2
34
50

can you please put some details so that I can modify accordingly, if required? – user187580 Apr 20 '10 at 17:34

score 0 · Answer 4 · edited May 23 '17 at 12:11

0

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.

edited May 23 '17 at 12:11

Community

1
1

answered Apr 20 '10 at 17:17

hookedonwinter

12,436
19
61
74

What if the keyword is at the end of a sentence? "I have an awesome house!" – Aaron W. Apr 20 '10 at 17:21
astander's solution is better than mine, as it allows the desc to start or end with the keyword too. – hookedonwinter Apr 20 '10 at 17:22
@Aaron ya, just realized that. Astander has the right answer. – hookedonwinter Apr 20 '10 at 17:22
I think that the **REGEX** option from [@Bears will eat you] seemed like a better solution, that is why I removed my answer X-) – Adriaan Stander Apr 20 '10 at 17:23
Ya, once I thought about punctuation I realize that was better too. So, @user187580, [@Bears will eat you] has the best answer, imo, but if you don't want to play with regex, see mine or @Karthik – hookedonwinter Apr 20 '10 at 17:26

score 0 · Answer 5 · answered Apr 20 '10 at 17:23

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%'

Searching a keyword in a long string in mysql?

5 Answers5