Include space in mysql like search

Question

I am having problem in using mysql like keyword in certain condition. My requirement goes like this. First, when i search for 'ABC', the result should find ABC and ABCdef but not xyzABCdef or xyzABC. It seems simple at first to use ABC%. but in condition when i search for 'heart%', it doesnot return row that have 'liver heart' because it returns only row that have heart at the beginning of the string.Then i tried using % heart%.This returned row having 'liver heart' but not those rows that have heart at the beginning of string. I am kind of stuck here..so guys help me.

EDIT With your help guys i came with the following solution but still its giving me some problem.

    SELECT q.question_id, q.question, q.date,p.fname,p.lname,p.phys_id,
p.pic_path  
            FROM questions q JOIN physiciansprofile p ON p.phys_id=q.phys_id   
         WHERE  q.question  LIKE 'heart%' OR question LIKE '% heart%' 
            AND q.question LIKE 'liver%' OR q.question LIKE '% liver%' 
    ORDER BY q.date DESC LIMIT 0,10;

But this query return heart failure and symptoms liver as well.Is there any solution for this. I need to get result containing both heart and liver and also must satisfy condition as stated before. Is there any to solve this

can you post your whole code?

Þaw
– Þaw

2013-06-21 06:27:50 +00:00
Commented Jun 21, 2013 at 6:27 — Þaw
– Þaw, Commented Jun 21, 2013 at 6:27

mcalex · Accepted Answer · 2013-06-21 06:31:20Z

6

'% heart%' doesn't work because you are asking for anything, plus space, plus heart, plus anything.

Try something like:

like 'heart%' OR like '% heart%'

answered Jun 21, 2013 at 6:31

mcalex

6,7985 gold badges53 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2014-11-27 12:50:58Z

3

I just saw this old unsolved request. As someone may have the same question, I'll post the answer in spite of the request's age.

WHERE q.question LIKE 'heart%' OR question LIKE '% heart%' 
AND q.question LIKE 'liver%' OR q.question LIKE '% liver%'

translates to

WHERE q.question LIKE 'heart%' 
OR (question LIKE '% heart%' AND q.question LIKE 'liver%')
OR q.question LIKE '% liver%'

because AND has precedence over OR.

So one must use parentheses here. This is often the case when mixing AND and OR and it's good habit to always do so:

WHERE (q.question LIKE 'heart%' OR question LIKE '% heart%')
AND (q.question LIKE 'liver%' OR q.question LIKE '% liver%')

answered Nov 27, 2014 at 12:50

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Comments

Þaw · Accepted Answer · 2013-06-21 06:36:11Z

1

what about

WHERE foo LIKE '% heart%' OR foo LIKE 'heart%'

edited Jun 21, 2013 at 6:36

answered Jun 21, 2013 at 6:29

Þaw

2,0474 gold badges22 silver badges40 bronze badges

7 Comments

ntstha Over a year ago

using %heart% would match theart as well.So that cant be used

Þaw Over a year ago

pardon sir? what exactly do you mean?

ntstha Over a year ago

i mean %heart% would match xyzheart as well. I mean searching should be reasonable.If user enter hea than result should include heart but not theatre and so on.So using %s% is not correct in my case.

Þaw Over a year ago

so your desired output is liver heart heart and heart failure?

ntstha Over a year ago

i need result containing both heart and liver not only heart or liver.It must be and

|

Collectives™ on Stack Overflow

Include space in mysql like search

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related