compare column value to anything in a list of values

Is there a simple function or method to compare a value in a column to one =
or more items in a comma separated list?

select * from table where value ('apple','orange','banana=
');

and say value may =3D something like 'produce(grape,orange,pear,apple,lettu=
ce)'=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: compare column value to anything in a list of values

--_000_0AC31FD51798B348BFB7EFD2BDEFE96E155455877BEXVMBX02012 ex_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

A simple IN would not match if the actual value of the values column is lit=
terally ''produce(grape,orange,pear,apple,lettuce)' and you want to see if =
any one of ('apple','orange','banana') is in there....

________________________________
From: drydell@optonline.net [drydell@optonline.net]
Sent: Wednesday, February 17, 2010 11:31 AM
To: Cantwell, Bryan
Subject: Re: compare column value to anything in a list of values

yes, there is... IN

SELECT * FROM tbl WHERE col IN ("v1", "v2", "v3")

----- Original Message -----
From: "Cantwell, Bryan"
Date: Wednesday, February 17, 2010 12:25 pm
Subject: compare column value to anything in a list of values
To: "mysql@lists.mysql.com"

> Is there a simple function or method to compare a value in a
> column to one or more items in a comma separated list?
>
> select * from table where value
> ('apple','orange','banana');
> and say value may =3D something like
> 'produce(grape,orange,pear,apple,lettuce)'--
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Ddrydell@optonline.net
>

--_000_0AC31FD51798B348BFB7EFD2BDEFE96E155455877BEXVMBX02012 ex_--

Re: compare column value to anything in a list of values

On Wed, February 17, 2010 09:24, Cantwell, Bryan wrote:
> Is there a simple function or method to compare a value in a column to one
> or more items in a comma separated list?
>
> select * from table where value
> ('apple','orange','banana');
>
> and say value may = something like
> 'produce(grape,orange,pear,apple,lettuce)'
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mussatto@csz.com
>
>
IN('value1','value2') should work for exact matches, also works for
integer values.

------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: compare column value to anything in a list of values

[snip]
IN('value1','value2') should work for exact matches, also works for
integer values.
[/snip]

IN will not open and read his CSV file...

[snip]
Is there a simple function or method to compare a value in a column to
one or more items in a comma separated list?
[/snip]

In order to do this you are going to use a programming language or
scripting language. For PHP you could put the values from the CSV list
in an array and the use IN to compare against that array.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: compare column value to anything in a list of values

> [snip]
> Is there a simple function or method to compare a value in a column to
> one or more items in a comma separated list?
> [/snip]

Take a look at SUBSTRING_INDEX and related string functions.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org