Thank you Mosh. Your code works perfectly. I thought there might be a way to convert column data from a string into a comma-separated list using whatever token you needed. So if a column row contained "1,2,3" (1 element) it would be converted to "1","2","3" (3 seperate elements). It was just a thought.
Anyway, thanks again for help. Brian --- Mosh Teitelbaum <[EMAIL PROTECTED]> wrote: > Brian: > > You probably want o be using wildcard matching > instead of the IN operator. > Your query should be changed to something like: > > WHERE ( > Emonth LIKE '%,#frmEMonth#,%' > OR Emonth LIKE '#frmEMonth#,%' > OR Emonth LIKE '%,#frmEMonth#' > OR Emonth = '#frmEMonth#' > ) > AND ... > > The first line matches records that contain the > number in the middle of the > field (1,3,2). The second line matches records that > contain the number at > the beginning of the field (3,1,2). The third line > matches records that > contain the number at the end of the field (1,2,3). > The last line matches > records that only contain that one number (3). > > Note that the above code assumes there are no spaces > between the commas and > the numbers. The reason why the commas are included > is to make sure that a > search for "3" doesn't return results that contain > but don't equal 3 (ex. > 13, 32, etc.). > > The IN operator is used when trying to match a field > to any number of > results. For example, to check a field "myField" to > see if it contains any > of the numbers from 1 to 3, you'd use code like: > > WHERE myField IN (1,2,3) > > -- > Mosh Teitelbaum > evoch, LLC > Tel: (301) 942-5378 > Fax: (301) 933-3651 > Email: [EMAIL PROTECTED] > WWW: http://www.evoch.com/ > > > > -----Original Message----- > > From: Brian Ferrigno [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, August 13, 2003 1:04 PM > > To: CF-Talk > > Subject: SQL problem using the IN function > > > > > > Hello all, > > > > I am attempting to return all rows from a table by > > searching for an integer value in a varchar column > > that contains a comma-seperated list of numbers. > > > > Right now the query is only returning rows that > are > > single values and not part of a comma-seperated > list. > > For example if I try to look for records that > contain > > a value of 3 in the EMonth column: > > > > ID EMonth > > ---------- > > 1 1 > > 2 3 > > 3 1,2,3 > > > > only row 2 will be returned. The query ignores row > 3 > > and any other row with a comma-sep list. > > > > THE CODE > > -------- > > SELECT ID as cardid, CardName, ELogo, Monthly, > ECatID > > FROM ECards > > WHERE '#frmEMonth#' IN (Emonth) AND monthly=1 > > <cfif fSubcatID>AND > > eSubcatID=#fSubcatID#</cfif> > > <cfif fCatID>AND ECatID=#fCatID#</cfif> > > ORDER BY sortCard > > -------- > > > > Thanks for any help. > > > > > > Brian > > > > > > > > __________________________________ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site > design software > > http://sitebuilder.yahoo.com > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
