Problem using LIKE operator

teslacoil · #1 (**permalink**) 06-09-11, 09:14

Hi everyone, after spending a lot of time searching for a solution, I'm still having a problem in one of my exercises.
This is what I have been asked to do:

*List name, type, price and description for all juices that include the word fruit but not fruits or fruit as part of another word (e.g. grapefruit).
This is the query I'm using:

Code:

SELECT juice_id, juice_name, juice_type, juice_price, juice_description
FROM tblJuice
WHERE juice_description LIKE '%fruit%'
AND juice_description NOT LIKE '%fruits%';

I cannot figure out how to not include fruit as part of another word (and I have been trying multiple combination but in vane.
Can anyone shed some light?
Thanks in advance.
xls file juiceData.xls
http://img194.imageshack.us/img194/6066/capturedgl.png

Could anyone load this table and post the query? It will help me a lot.
Considering that the lesson for LIKE operator is:

Code:

1) Uses wildcards to test for pattern match
2) % represents any collection of characters
3) _ (underscore) represents any single character
Note searches using wildcards can be slow to process

and the examples are:

Code:

List name and description of all juices where description includes ‘orange’
e.g.1
 SELECT juice_name, juice_description
FROM Juice
WHERE juice_description LIKE '%orange%‘
e.g. 2
WHERE name LIKE ‘T_m’  would return ‘Tim’, ‘Tom’ etc.

I believe I have to work out the solution by using only the syntax in the LIKE Operator from the code above.

Many thanks
S969

mike_bike_kite · #2 (**permalink**) 06-09-11, 10:05

Quote:

Can anyone shed some light?

I don't think you can do it with one like clause but if you accept that fruit just needs to have a space on either side or come at the start or end of the sentence then you can do it with multiple likes and not likes. Alternatively you could look at rlike but that will need some understanding of regular expressions.

teslacoil · #3 (**permalink**) 06-09-11, 10:30

Thanks Mike, you made me realized that I was missing a space in '%fruit%.
I have solved it by running this query:

SELECT juice_id, juice_name, juice_type, juice_price, juice_description
FROM tblJuice
WHERE juice_description LIKE '% fruit %'

Thanks again
s969

mike_bike_kite · #4 (**permalink**) 06-09-11, 11:11

Would that match where juice_description is "passion fruit" or "fruit cocktail" ?

teslacoil · #5 (**permalink**) 06-09-11, 15:08

Hi Mike, this exercise contains the solution and it shows only three records
1) Sparkling fruit juice
2) Refreshing fruit mix strawberry chunks
3) Sparkling summer fruit blend
If there was "passion fruit" or "fruit cocktail" in to the .xls, I think it should have showed them, but I notice that the question asked:
'to not include fruit and fruits as part of another word.'
So, I think that the real solution it should show even
'Blended tropical fruits' and 'Summer fruits with mint', since fruits is not part of another word.
Now I have sent a email to my tutor to see if I am right or wrong about my finding.
Thanks Mike

EDIT: I was wrong. "that include the word fruit but not fruits or fruit as part of another word (e.g. grapefruit)."
The 'or' is the solution to my last question.

So the query:
SELECT juice_id, juice_name, juice_type, juice_price, juice_description
FROM tblJuice
WHERE juice_description LIKE '% fruit %';
it is the right one

r937 · #6 (**permalink**) 06-09-11, 16:04

Quote:

Originally Posted by teslacoil

WHERE juice_description LIKE '% fruit %'

that's not a comprehensive solution, as it will return rows only if the specific word "fruit" is both preceded and followed by a space

it won't, for example, find "fruit drink" because there's no preceding space

i hate handing out homework assignment solutions, so i'll simply mention that what you want is REGEXP instead of LIKE, using word boundary markers

there are some pretty decent examples right there in the mysql manual

teslacoil · #7 (**permalink**) 06-09-11, 22:22

Hi r937,
You are right, I didn't think of it, but I haven't done yet the REGXP.

Quote:

Considering that the lesson for LIKE operator is:

Code:

1) Uses wildcards to test for pattern match
2) % represents any collection of characters
3) _ (underscore) represents any single character
Note searches using wildcards can be slow to process

and the examples are:

Code:

List name and description of all juices where description includes ‘orange’
e.g.1
 SELECT juice_name, juice_description
FROM Juice
WHERE juice_description LIKE '%orange%‘
e.g. 2
WHERE name LIKE ‘T_m’  would return ‘Tim’, ‘Tom’ etc.

I believe I have to work out the solution by using only the syntax in the LIKE Operator from the code above.

Internet Services

Web Development

Digital Marketing

Consumer Tech