Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Filter by specific number

    I have a field which contains the following: 8, 28, 38, 48
    How do I filter for just one number, for example 8?

    For example
    SELECT id_img
    , theme_img
    FROM image_img
    WHERE theme_img
    LIKE '%8%'

    Does not work!

    Nick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    I have a field which contains the following: 8, 28, 38, 48
    are you saying that different rows might have those different values?

    or are you saying that a single row might have a value consisting of four numbers with comma delimiters?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    124
    A single field in a singel row. Right or wrong I didn't want to have a column for each theme, or medium etc. I implode the values from an array of checkboxes into a single string like the one above, 8, 28, 38, 48. I could go backan use the words labels instead of the ids and the LIKE might work better.

    My goal was a lean database we are hoping for a lot of business. A lot of donations to non-profits.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whether or not your goal was a lean database, you've munged up your queries

    using words instead of ids is only marginally better, but still terrible

    there's a reason first normal form was invented

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Posts
    4
    You shuld use query like this:

    SELECT id_img
    , theme_img
    FROM image_img
    WHERE theme_img
    LIKE '%8,%'

    Notice I added comma symbol after number "8". This will work except the last number, couse in your table you don't have comma at the end. So before you can use query above, first you need to update this field by adding one comma at the end.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, if you're going to do it that way, here's the sql --

    WHERE CONCAT(',' , theme_img , ',') LIKE '%,8,%'

    it's still going to result in a table scan, which will not scale, i.e. the more rows in the table, the slower the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I have a field which contains the following: 8, 28, 38, 48
    How do I filter for just one number, for example 8?

    For example
    SELECT id_img
    , theme_img
    FROM image_img
    WHERE theme_img
    LIKE '%8%'

    Does not work!

    Nick
    Nick, what does not work? Are you getting all the rows that have %8% and you only want the row with the number 8? Are you getting an error, meaning your column is defined as a number and your LIKE statement will not work? We can sit here and throw all kinds of stuff out to you, but most will not be helpful.
    Dave

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dav1mo View Post
    Nick, what does not work?
    it returns too many results, including incorrect ones

    theme_img
    1,5,8,9,37
    2,23,48,94
    3,8,18,28

    he's searching for "8" and will get back the first and third rows (correct) but also the second row (incorrect)

    this would have been obvious if you had read all his posts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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