Well, the only problem is that won't match the first and last elements in that field, nor if it is the element in that field, so you really need:
SELECT EmailAddr, RecipientID FROM SupportEmailRecipient <cfif ListLen(Form.SubjectID)> WHERE (SubjectID LIKE '%#FORM.SubjectID#,%' OR SubjectID LIKE '%,#FORM.SubjectID#,%' OR SubjectID LIKE '%,#FORM.SubjectID#%' OR SubjectID = '%#FORM.SubjectID#%') </cfif> Or did I miss something obvious? - Calvin ----- Original Message ----- From: "S. Isaac Dealey" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, July 01, 2003 6:47 PM Subject: Re: SOT: Correct way to match a record with a list in one field? > It doesn't have to be _horribly_ creative... As long as you only need to find one element in the list, you should be able to use this: > > SELECT EmailAddr, RecipientID > FROM SupportEmailRecipient > WHERE SubjectID LIKE '%,#FORM.SubjectID#,%' > > I'm not storing comma delimited lists in the database, but rather, in cases where I need or want an IN () statement that I couldn't otherwise accomplish by passing a list to a stored procedure ( because I want to continue using a stored procedure ) I'm passing the list and selecting WHERE @mylist LIKE '%,' + cast(mycolumn as nvarchar) + ',%' which seems to work fairly well... > > hth > > > Normalize your SubjectID column...otherwise you will need > > to either > > get creative with the string functions in sql, or do the > > compare with > > CF. > > > -- > > jon > > mailto:[EMAIL PROTECTED] > > > Tuesday, July 1, 2003, 2:58:55 PM, you wrote: > > LF> Ok guys.. Thinking caps: ON > > > LF> Have an nvarchar field in the database with data like: > > 1,3,9,12,2,51 > > > LF> Have a formfield with single number (not digit) data, > > like: 1 or 12 -- > > LF> single whole numbers, maybe more than 1 digit. > > > LF> Now, would like to do something like this: > > > LF> SELECT EmailAddr, RecipientID > > LF> FROM SupportEmailRecipient > > LF> WHERE SubjectID IN ('#FORM.SubjectID#) > > > LF> It ain't workin... Need to see if the data from the > > formfield is IN the data > > LF> in the table's record. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > ~~~~~~~~~~~| > > 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 > > > Signup for the Fusion Authority news alert and keep up > > with the latest news in ColdFusion and related topics. > > http://www.fusionauthority.com/signup.cfm > > > Unsubscribe: http://www.houseoffusion.com/cf_lists/uns > > ubscribe.cfm?user=633.558.4 > > > > > s. isaac dealey 972-490-6624 > > new epoch http://www.turnkey.to > > lead architect, tapestry cms http://products.turnkey.to > > tapestry api is opensource http://www.turnkey.to/tapi > > certified advanced coldfusion 5 developer > http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
