SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: CONTAINS in row

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CONTAINS in row

    Hi

    Is there any option to check if a character exist in a row

    for example i have table as below

    id name numvals
    1 a 1,2,3
    2 b 2,3,4,5,15
    3 c 13,14,1,3,5
    4 d 20,2,200
    5 e 50,2,200
    6 f 15


    Code:
     SELECT * from `table` WHERE `numvals` NOT IN (5)
    Im looking for alternative for NOT IN so that rows returned will be

    1 a 1,2,3
    4 d 20,2,200
    5 e 50,2,200
    6 f 15


    Thanks in advance

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    The solution is normalizing your table. Comma separated values in a table column indicate there's something wrong in your database design, that'll create you problems sooner or later (as you found out right now ).

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tried something like this, im getting the results but still testing
    Code:
    SELECT *,FIND_IN_SET('5',numvals) as `existval` FROM `table`  WHERE FIND_IN_SET('5',numvals)=0

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Doesn't scale well. Normalize your data as suggested.

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your comment, i will try that way


Bookmarks

Posting Permissions

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