IN operator

i need do a conditional test with a number value and a comma separated list..

i.e.
in mssql this is exactly what it would be.

NOT #group_id# IN #group_id_list#

but i need to pull it off in coldfusion..

i.e.
<cfif NOT group_id IN group_id_list>
...
</cfif>

but that obviously doesn't work.. is there an operator in coldfusion that works the same way?

 

i figured it out immediately after i posted that, i apologize..

if anyone cares, it was

<cfif group_id_list DOES NOT CONTAIN group_ID>

 

Don't use CONTAINS with lists. It may return inaccurate results. Use list functions like listFind() or listFindNoCase().

 

sorry, i didn't mean it was a variable of type list..

the variable is simply set like this

<cfset varname="num1,num2,num4">

should i still be staying away from the contains?

 

Yes because CONTAIN doesn't only search for exact matches.

If your comma delimited list of values is
<cfset varname="1,22,4,88">

And you search for the value "2", CONTAIN will return true even though the list doesn't contain the exact value "2". List functions search for exact matches only.

 

If you are filtering directly from the mssql database, you can do something like this:

<cfset myFilter = '1,2,3,4,5,6,7,8,9,10'>

<cfquery datasource="dsnName" name="SOME_LIST">
SELECT FIELD_ONE, FIELD_TWO
FROM TABLE_NAME
WHERE FIELD_TWO IN (#ListQualify(myFilter,"'",",","ALL")#)
</cfquery>

Hope this helps.

Sincerely,
Travis Walters
admin@codebuyers.com

 

Its easier to use cfqueryparam. Its works great with lists and automatically handles quoting.

Not tested. Watch out for typos

<cfset yourNumericList = "1,2,3,4">
<cfset yourStringList = "item1,item2,item3,item4">

<!---- numeric column type --->
WHERE FIELD_TWO IN 
(
<cfqueryparam value="#yourNumericList#" cfsqltype="cf_sql_integer" list="true">
)

<!---- string column type --->
WHERE FIELD_TWO IN 
(
<cfqueryparam value="#yourStringList#" cfsqltype="cf_sql_varchar" list="true">
)

Code (markup):

 

WHOA guys, calm down! LOL!!!

We've got some talented people here - keep up the good work!

 

I'm lost Why would you say calm down? I'm certainly not feeling any hostility and didn't think twalters84 was either. We presented two options. End of story

Sorry for the excess emoticons, but obviously something got lost or turned around in the original posts. LOL!