• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

SQL query of a ColdFusion List

I have a query and I'm trying to see if one of the SQL variables is LIKE one of the items in a list. At the end of the query I have

  AND tblPerson_RTIDetail.personUnit IN (#PreserveSingleQuotes(session.userRoleUnits)#)

This appears to be doing only a direct match. Can I use "LIKE" in place of "IN" and, if so, how would I place the %?

Thanks a lot!
  • 2
2 Solutions
You would put the % whereever you want to use the wildcard.  For instance if you wanted to find all cities that started with m you would write.

from cities
where name LIKE 'm%'

This will return all cities that start with m

Can I use "LIKE" in place of "IN"

If #session.userRoleUnits# contains multiple values - then NO.  What you could do is loop through the list and generate a LIKE statement for each one as @nmarano described above

WHERE  .....
<cfloop list="#session.userRoleUnits#" index="unit">
    AND tblPerson_RTIDetail.personUnit LIKE '#unit#%'


( I've probably mentioned it before, but that's a very dangerous function to use on sql server )
traportAuthor Commented:
Yes it will have multiple values.

Why is it a dangerous function? Thanks for bringing it to my attention.

I'll go try this now.
Ok, then you'll need a loop combined with @nmarano's example of LIKE.  But use cfqueryparam too

  AND tblPerson_RTIDetail.personUnit LIKE <cfqueryparam value="#unit#%" cfsqltype="cf_sql_varchar">

Why is it a dangerous function?
It makes queries vulnerable to a common type of sql injection.  

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now