SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL IN statement: does ID exist in a comma separated string

    Hi,
    I have a property database and one of the fields contains a comma separated list/string which are property types; this field data looks like: 1,2,3,4,5,6

    What I need to do is to query the database and ask whether an ID (a variable) (property type) exists in that string. In plain english I'd say: is "2" in "1,2,3,4,5,6"?

    I've tried using "IN" but it doesn't work as it's the database field that contains the comma separated string and the ID is the external variable/query - i.e. I've seem examples of an IN statement like this:

    (in all of these examples I'll use "2" as an example of a query variable)

    SELECT * FROM property_tbl
    -> WHERE property_type IN (1,2,3,4,5,6);

    but in my case I need it to be the other way around:

    SELECT * FROM property_tbl
    -> WHERE 2 IN (property_type);

    (where a value of property_type might be "1,2,3,4,5,6")

    At the moment I'm using an ugly method to get this working that looks like this:

    SELECT * FROM property_tbl
    -> WHERE (property_type LIKE ('2') OR property_type LIKE ('2%') OR property_type LIKE ('%2') OR property_type LIKE ('%2%'))

    this works but I figured there must be a better way of doing it.

    Can anyone help.
    Many thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the only better way of doing it is to normalize the table

    any time you store multiple values inside a single column, you are asking for exactly this type of trouble (it violates first normal form, in case you want to do some research)

    it's okay to store it that way, if you only ever retrieve it by table key, but if you try to search inside that column, you get this nasty ****-up

    for one thing, it doesn't scale, because it requires a table scan, so the more rows in the tables, the slower the search

    here's a more compact form of the WHERE condition...
    Code:
    WHERE CONCAT(',' , property_type , ',') LIKE '%,2,%'
    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    Thanks very much for the speedy reply and the for the helpful advice. Looks like normalizing the data is the way to go.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Normalizing is almost always the way to go.


Tags for this Thread

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
  •