SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding a number in a list query

    I have a field (Mtypes) in table TypeData that contains a comma delimitted list of numbers (1,2,3,4,10,13,25). What is the best way to find all the records that have mtypes that contain "1"?
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the best way?

    normalize your table

    instead of table TypeData having a single column with multiple values in it, you need (possibly in a separate table) a single column with single values in multiple rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Although that would be the best way to do it, I do not control and manage the database, I just get to query it so I'm stuck working with it the way it is.

    I guess one option would be to build a temp table on the fly and populate it one row at a time with the values in field and then query the new table, but i think there has to be a way to query the original table and get the values I need
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i'm sorry to hear about your sad situation, i've been there and in fact i'm the guy who posed for the picture on the t-shirt

    try this:

    ... where concat(',',mtypes,',') like concat('%,',searchterm,',%')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I got something to work, and can now build from there. I'm using ColdFusion so I just built a new query in memory using CF function QUERYNEW, that "normalized" the table and then queried that data set to get the data I need. It's not the quickest method, but speed wasn't an issue in this case.

    I also tried your suggestion of using

    where concat(',',mtypes,',') like concat('%,',searchterm,',%')

    but I'm using SQL Server 2000 and it would throw an error saying
    Server: Msg 195, Level 15, State 10, Line 5
    'concat' is not a recognized function name.

    Thanks for your help, you got me going in the right direction
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, duh

    i am an idiot ® © ™

    i gave you mysql syntax

    that's because it's almost always some php programmer in love with the $explode function who stores a comma-delimited list in a column

    i should have given you standard sql syntax

    sql server would've barfed on that too, though

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Dangermouse's Avatar
    Join Date
    Oct 2003
    Posts
    1,024
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:

    $explode function? That would be a variable, i think you mean explode()

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm certain that's what i meant

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •