Filter a CFQUERY using a list of data separated by a semi-colon

I have a pretty standard form where the user can enter data into one or more of several text boxes and search a database for info.  I want to take this further by allowing the user to enter more than one search parameter within one text box separated by semi-colons.  The best way to explain this is to show a simple example:

EG.  Currently the user can enter all or part of an item number of a product.  In the cfquery I use:

WHERE itemno like '%#item_number#%' . . . .
. . . . So, if they entered "12345" into the text box, the query would search for and return any item number containing "12345"

WHAT I NEED:  is for the user to be able to enter more than 1 item number within the same text box separated by semi-colons:


How do I change my "WHERE" statement in the query so it will search for each item number found between the semi-colons?  I know a little about lists, but cannot get this to work.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What if you just looped through the list?  Something like this??

<cfloop    index = "item_number"   list = "item_list"   delimiters = ";">
OR itemno like '%#item_number#%'


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
expanding on the above answer...

FROM ...
<cfloop    index = "item_number"   list = "item_list"   delimiters = ";">
OR itemno like '%#item_number#%'

This isn't ideal as it leaves you potentially open for SQL injection attacks, but if it's in a closed system where that concern is minimal or non-existant, you could try something like this:

WHERE itemno IN ('#Replace(item_number, ";", "', '", "ALL")#')

If, instead, you need it to be as secure as possible, try something like this:

WHERE itemno IN (
  <cfqueryparam value="#item_number#" cfsqltype="CF_SQL_NUMERIC" separator=";" list="yes">
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Actually, my suggestion isn't going to do LIKE comparisons.  So, if that's important to do, you'll need to use a loop with an OR condition.
Here is my suggestion which is more complete than my last post and addresses the security concern.

<CFQUERY datasource="#dsn#" name="queryname">
      SELECT *
      FROM tablename
      WHERE 0=1
      <cfloop    index = "item_number"   list = "#item_list#"   delimiters = ";">
            OR item_no like <CFQUERYPARAM value="%#item_number#%" cfsqltype="CF_SQL_VARCHAR">
MaxwellTurnerAuthor Commented:
Thanks for the replies.  I was side-tracked for abit, but I am going to try tackle this now.

I want it to remain possible to just do a simple LIKE comparision as well as provide the option for retrieving multiple paramters with a semi-colon delimiter.  The WHERE portion of my query is massive (about 1500 lines) so this is going to be a little bit of work.

Dan - What is with the "WHERE 0=1" - what is the 0=1 for?

Without the 0=1 to start off the where clause's the first thing you would end up with when the loop first ran would be

Where OR item_no like...etc which wouldn't work.  

so you start off with Where 0=1 so you can get

Where 0=1 OR item_no like... which will work.

You want to use "where 0=1" whenever you use OR's and "where 1=1" whenever you use AND's.
This 1 = 0 and 1 = 1 advice all hinges on whether or not you have existing conditional statements you need to add these bits too.  If you do, ignore the advice of 1 = 0 and 1 = 1 as it's completely unnecessary.
MaxwellTurnerAuthor Commented:
I get it now - "0=1" - I should have figured that one out!  I didn't look close enough at the code inside the loop before I asked that.

Sorry I have not replied here for the last few days - I got hammered by a virus and had to take care of that first.  

I have never used an index loop before, but it worked perfectly!

Thanks a million guys - I would have never figured that out on my own.  I'll split the points!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.