Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: [Solved] Problem using LIKE operator

    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
    Last edited by teslacoil; 06-09-11 at 11:50.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    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
    Last edited by teslacoil; 06-09-11 at 11:44.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Would that match where juice_description is "passion fruit" or "fruit cocktail" ?

  5. #5
    Join Date
    Jun 2011
    Posts
    4
    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
    Last edited by teslacoil; 06-09-11 at 19:03.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by teslacoil View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2011
    Posts
    4
    Hi r937,
    You are right, I didn't think of it, but I haven't done yet the REGXP.

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •