using list in subquery

[spacehog]

I have a query that I would like to pass a list of values to use in the WHERE
statement. How do I use a list in the WHERE statement?

Example:
<cfset list = "a,b,c">
SELECT *
FROM table
WHERE id IN ('#list#')

ColdFusion reads the above query as:
SELECT *
FROM table
WHERE id IN ('a,b,c')

It read the list as a single string instead of:
SELECT *
FROM table
WHERE id IN ('a',b','c')

I remember hearing of a way to insert the needed quotes, but I have forgotten.

[zoeski80]

Hey SpaceHog

I think ListQualify is the function you're after.

Zoe

[anclarke]

As long as you're not using a stored procedure, just do something like this:
where id in ('#replace(list, ',', '','', 'ALL')#') So, your ids are character
strings and not numeric? If they're numeric and you're using MSSQL, you'll
just want to do it like: where id in (#list#) It doesn't matter which way you
do it if you're using MySQL, IIRC. Now I have a question for you: how do you
get your code to look all nice like that when you post it in this forum? HTH
&amp; Thanks :-) - Andrew.

[anclarke]

Hmm duh yeah. Forget what I said: use listQualify(). I never do that myself so I didn't even know that existed!

- Andrew.