SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with commas in a field

    I am working with a database (not mine) where the values in the field can be some thing lke:
    Code:
    ID | FIELD
    ----------
    1      2,4,6
    2      23,42
    3      2
    4      32, 33
    etc...
    I need to be able to grab the rows based on one of the values separated like 2 but not get 23. And I can't assume a comma like '2,' because 2 could be the final number or part of 32,. I'm hoping this is doable in the query so I can avoid having to grab them all and process seperately.

    Thanks,

    baze

  2. #2
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you trying to match a value in the list? I.e. find whether the number 2 exists in one of the lists in your table?

    If so use MySQL's FIND_IN_SET function like so:

    Code:
    SELECT *
    FROM table
    WHERE FIND_IN_SET(2, fieldname)

  3. #3
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dean C
    Are you trying to match a value in the list? I.e. find whether the number 2 exists in one of the lists in your table?

    If so use MySQL's FIND_IN_SET function like so:

    Code:
    SELECT *
    FROM table
    WHERE FIND_IN_SET(2, fieldname)
    With the data I have, that looks like it will do the trick. Thanks.

    vgarcia, the table is a vbulletin table, kinda gotta leave it as is. I suppose for their purposes (the way they use it) it works fine. I'm writing some stuff that that will interface with it.

    thanks for your help,

    baze

  4. #4
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Fix that database. That's a terrible way to store data and it violates some normalization best practices. Your data should look more like this:
    Code:
    ID | FIELD
    ----------
    1      2
    1      4
    1      6
    2      23
    2      42
    3      2
    4      32
    4      33
    Then you could search the database properly . I know this might be some extra work on your part but it will save you lots of time down the road.


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
  •