MySQL Select with LIKE and WHERE

Question

I have a table of biographical data, and I need to do a query that selects people who have the word "died" in their bio and whose death date is NULL. This doesn't work:

SELECT * FROM people
WHERE bio LIKE '%died%'
AND death_date IS NULL

That selects everyone whose death_date is null, but also selects people who don't have the word "died" in their bio. Can I do this in one query?

Are you sure that it returns people who don't have 'died' in their bio? Remember it will also include words containing died such as studied, and imbodied. — Kibbee, Nov 19 '10 at 16:56

score 5 · Answer 1 · answered Nov 19 '10 at 16:51

5

Perhaps the problem is the quotes, use " instead of ' to wrap the LIKE clause.

SELECT * FROM people
WHERE bio LIKE "%died%"
AND death_date IS NULL

answered Nov 19 '10 at 16:51

Ben

60,438
111
314
488

Running the query like this returns the same results. – kirkaracha Nov 19 '10 at 16:59
What data type is the "bio" field? – Ben Nov 19 '10 at 18:16

score 5 · Accepted Answer · answered Nov 19 '10 at 16:53

5

It could be a word like 'completelydied' and they are still going to be selected. Check carefully if 'died' phrase does not exists as part of some word in the records that you got selected.

answered Nov 19 '10 at 16:53

Yasen Zhelev

4,045
3
31
56

1

Do not forget that adding space in front of 'died' will not work if the word is in the beginning of the bio. – Yasen Zhelev Nov 19 '10 at 17:03

score 0 · Answer 3 · answered Nov 19 '10 at 16:55

0

i would suggest you make bio upper case and have like "%DIED%" also make sure that death_date is really null, see if you get an answer from both where cases independently. What response are you getting?

answered Nov 19 '10 at 16:55

Mauro Morales

1,517
2
11
12

Using WHERE death_date IS NULL by itself returns the people whose death_date is NULL. – kirkaracha Nov 19 '10 at 16:59
what about? WHERE UPPER(bio) LIKE '%DIED%' – Mauro Morales Nov 19 '10 at 17:01

MySQL Select with LIKE and WHERE

3 Answers3