Put this WHERE statement in your query. change fldl to whatever the field is called.
<CFSET comparelist = "1,8,100">
WHERE (<CFLOOP INDEX="i" LIST="#comparelist#" DELIMITERS=","><CFIF
IsDefined("flag")>OR <CFELSE><CFSET flag = "on"></cfif>(fldl LIKE '#i#')</cfloop>)
On Jun 12, 2001 at 2:25 PM, you wrote...I have a db table with a text field that has
comma separated values, such
> as:
>
> fld1
> ----------------------
> Record 1| 1,3,5,7 |
> Record 2| 2,4,6,8 |
> Record 3| 100,120 |
>
> I also have a list (call it "comparelist") such as:
>
> 2,8,100
>
> I want to pull all records from the database where a single value in the
> fld1 list is contained in the comparelist. For example, given the above
> data, Record 2 and Record 3 would be retrieved. Basically I'm comparing two
> csv lists to each other.
>
> How would I design a select query to accomplish this? If the db were
> designed so that fld1 contained only 1 discrete value, I could use the IN
> function (along the lines of: where fld1 IN(comparelist) ). But in this
> situation (and it is not an option to redesign the db) I somehow need to
> compare each value in the db field to comparelist.
>
> Thanks for help.
>
> Paul Sinclair
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
