Yet another MYSQL question

Discussion in 'OT Technology' started by RyanL, Nov 7, 2005.

  1. RyanL

    RyanL OT Supporter

    Joined:
    Nov 30, 2004
    Messages:
    4,584
    Likes Received:
    0
    Location:
    St. Paul, MN
    I have a field, we'll call it field1, that constists of "1,2,3,4" or similar (IE "1,3" or "1,5,2").

    Is there anyway that I can select a query of data where field1 contains "1" or "2"?

    Something along the lines of "SELECT * FROM table WHERE field1 CONTAINS 1,2"


    BTW I know that isn't proper syntax, I was using it as an example.
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    SELECT * FROM table WHERE field1 LIKE '%1%' AND field1 LIKE '%2%'
     
  3. RyanL

    RyanL OT Supporter

    Joined:
    Nov 30, 2004
    Messages:
    4,584
    Likes Received:
    0
    Location:
    St. Paul, MN
    thanks
     
  4. Pinan

    Pinan pagan holidays > * other holidays

    Joined:
    Apr 17, 2001
    Messages:
    755
    Likes Received:
    0
    Location:
    St. Louis area
    Maybe I am mistaking the question, but I think he is asking to return values where there is either a 1 or a 2 only in there. Wont your SQL return any row that has a 1 or a 2 in it? Like 2,3,4 as well as just 2?

    If I am mistaken, sorry about that. But if you want to get rows when the values are only 1 or 2, then try this...

    SELECT [what columns you need]
    FROM [tablename]
    WHERE field1 IN ('1', '2')

    one note BTW, not sure if this will work with MYSQL (it will with Teradata)
     
  5. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    it will work because of the AND. It will look for fields that contain the text '1' AND the text '2'. Granted, there may be better ways of doing it (especially with mysql 5.0 out and the use of UDF's) but it works. I think, for the record, that the question was an over-simplification of a real problem anyways, :dunno:
     
  6. RyanL

    RyanL OT Supporter

    Joined:
    Nov 30, 2004
    Messages:
    4,584
    Likes Received:
    0
    Location:
    St. Paul, MN
    What is the order of operations for MySQL?

    Specifically I need to use OR because I need a 1 or a 2 not a 1 and a 2. Which is fine but they all need to have a specific date.

    So can I have it be something like...

    SELECT * FROM table WHERE date=x AND field1 LIKE '%1%' OR date=x AND field1 LIKE '%2%'
     
  7. RyanL

    RyanL OT Supporter

    Joined:
    Nov 30, 2004
    Messages:
    4,584
    Likes Received:
    0
    Location:
    St. Paul, MN
    SELECT * FROM table WHERE (date=x AND field1 LIKE '%1%') OR (date=x AND field1 LIKE '%2%')

    Yeah, I just found out you can use parenthesis.
     
  8. Pinan

    Pinan pagan holidays > * other holidays

    Joined:
    Apr 17, 2001
    Messages:
    755
    Likes Received:
    0
    Location:
    St. Louis area
    The LIKE command is still going to screw you up if you are looking for a 1 or a 2. With the LIKE command, you will get row which field has a 1 or a 2 anywhere in it. earlier in the thread, you mentioned that you needed it to be able to specify a 1 OR a 2. And specifically not a 1 and a 2, which with the LIKE command, it could return a 1 or a 2 or a 1,2 or a 2,1 and the combintations just goes on with the addition of more numbers.

    Bear in mind that I dont know specifically what you are looking for, nor do I know the data set, so your code could work the way you want it to. I just try to caution people with using the LIKE command.
     

Share This Page