SQL query of a ColdFusion List

Posted on 2011-05-10
Last Modified: 2012-05-11
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!
Question by:traport
    LVL 2

    Assisted Solution

    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.

    SELECT *
    from cities
    where name LIKE 'm%'

    This will return all cities that start with m

    LVL 51

    Expert Comment

    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 )

    Author Comment

    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.
    LVL 51

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now