#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Evanston, IL 60202
    Posts
    10
    Rep Power
    0
    I have a row in a table that has the following value in a column... "41220,41222,41223,41224,41225"

    How can i write this query to select the row if it contains one of the values...

    selectt * from orders where value = 41220

    is there a contains parameter or anything like that to accomplish this?

    Thanks in advance,
    Mike Dill
  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    Either of these should work:

    FIND_IN_SET(str,strlist)

    Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a `,':

    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
    -> 2

    INSTR(str,substr)

    Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the arguments are swapped:

    mysql> select INSTR('foobarbar', 'bar');
    -> 4
    mysql> select INSTR('xbar', 'foobar');
    -> 0

    Let me know if I can explain more...Those are from the manual.

    ---John Holmes...

    ------------------
    *************************************************************
    * The manual can probably answer 90% of your questions...
    *
    * PHP Manual. www.php.net/manual
    * MySQL Manual: www.mysql.com/documentation/mysql/bychapter
    *************************************************************
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Evanston, IL 60202
    Posts
    10
    Rep Power
    0
    thanks for your response. i tried your suggestion but i'm not sure it's what i'm looking for. ideally, i want to run a query that looks something like this:

    select * from orders where find_in_set(41220, addresses)

    addresses is the column that i want to search for the presence of 41220.

    this obviously didn't work. anyone know of anything similar that might?

    thanks,
    mike

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    Either of these should work:

    FIND_IN_SET(str,strlist)

    Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a `,':

    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
    -> 2

    INSTR(str,substr)

    Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the arguments are swapped:

    mysql> select INSTR('foobarbar', 'bar');
    -> 4
    mysql> select INSTR('xbar', 'foobar');
    -> 0

    Let me know if I can explain more...Those are from the manual.

    ---John Holmes...

    [/quote]

  6. #4
  7. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Colchester, England
    Posts
    131
    Rep Power
    15
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by mikedill:
    ideally, i want to run a query that looks something like this:

    select * from orders where find_in_set(41220, addresses)

    addresses is the column that i want to search for the presence of 41220.

    [/quote]

    Try this

    SELECT * FROM orders
    WHERE addresses LIKE '%41220%';

    Andy J
  8. #5
  9. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    Or use instr(), it might be faster than using LIKE..

    SELECT * FROM orders WHERE INSTR(addresses,'41220');

    Looking back...FIND_IN_SET should work if you put quotes around the number. you're looking for a string in a string...not an integer in a string... Did it give you an error or just not return any results?

    Choose the method that gives you the fastest result time.

    ---John Holmes...
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Evanston, IL 60202
    Posts
    10
    Rep Power
    0
    thank you. this was exactly what i was looking for.
    mike

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    Or use instr(), it might be faster than using LIKE..

    SELECT * FROM orders WHERE INSTR(addresses,'41220');

    Looking back...FIND_IN_SET should work if you put quotes around the number. you're looking for a string in a string...not an integer in a string... Did it give you an error or just not return any results?

    Choose the method that gives you the fastest result time.

    ---John Holmes...
    [/quote]

  12. #7
  13. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    Which one?

    LIKE?
    INSTR?
    FIND_IN_SET?

    ---John Holmes...
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Evanston, IL 60202
    Posts
    10
    Rep Power
    0
    select * from orders where instr(column, '$value')


    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by SepodatiCreations:
    Which one?

    LIKE?
    INSTR?
    FIND_IN_SET?

    ---John Holmes...
    [/quote]

Similar Threads

  1. question about vb 6, query and excel
    By rt_hide in forum Visual Basic Programming
    Replies: 0
    Last Post: February 17th, 2004, 03:43 AM
  2. simple search query
    By SeenGee in forum PHP Development
    Replies: 1
    Last Post: February 10th, 2004, 01:30 PM
  3. (hopefully) simple SQL in java servlet question
    By djstyli in forum Java Help
    Replies: 3
    Last Post: February 5th, 2004, 08:58 PM
  4. simple query help?
    By dwan in forum MS SQL Development
    Replies: 6
    Last Post: January 13th, 2004, 11:12 AM
  5. Simple question about sql function options
    By Salizar in forum ASP Programming
    Replies: 2
    Last Post: January 10th, 2004, 12:21 PM

IMN logo majestic logo threadwatch logo seochat tools logo