This is going to get off topic butany time you want to find an idividual value in this comma seperated list your design begins to enter the bad design area. And it does break normailization rules...
Adam H On Fri, 25 Feb 2005 14:00:10 -0000, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > Yes, strong comma separated lists in a DB is not ideal - though it is not > bad design as in some cases it does make sense - and it does not break any > 'rules'. > > -----Original Message----- > From: Adam Haskell [mailto:[EMAIL PROTECTED] > Sent: 25 February 2005 14:01 > To: CF-Talk > Subject: Re: sql question > > First off i want to point out that anytime you do something like it > should occur to you that this is probably not the best DB design and > could lead to more issues down the road...if this is indeed on of the > rare cases where this design makes sense. Then one option is to do > this CHARINDEX(','+item+',',search_item,0) > 0 > where search item would be something like ',11,' > You could also do a simular thing with LIKE.... ','+item+',' LIKE '%,11,%' > > Adam H > > On Fri, 25 Feb 2005 08:41:20 -0400, Protoculture <[EMAIL PROTECTED]> wrote: > > I have a list of items in a db field [items]... > > > > 1,11,14,12,13,17,15,18,19,16,4 > > > > Now I want to test that my item is one of those numbers thats is in that > field and select that row. See below. If I have one number (11), I want it > to select that row, so it needs to search the string of numbers in the field > [items], unless there is a way to use IN keyword in this case? any ideas? > > > > eq.. > > > > select * from myTable > > where items = 11 > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196514 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
