Not true, certainly all of this is unnessecary since the database should be designed differently anyway but just doing a talent LIKE '%dancing%' would be even worse. What happens if there are two things like dancing and dancings in there. Definitley not accounting for possibilities.
Tyler Clendenin GSL Solutions -----Original Message----- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 1:38 PM To: CF-Talk Subject: RE: SQL : Finding element in list of values in a query field. it would work to do... select * from [table] where talent like '%dancing%' nothing else is necessary :) tony weeg sr. web applications architect navtrak, inc. [EMAIL PROTECTED] www.navtrak.net office 410.548.2337 fax 410.860.2337 -----Original Message----- From: Tyler Clendenin [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 2:40 PM To: CF-Talk Subject: RE: SQL : Finding element in list of values in a query field. Would it work to just do SELECT * FROM [table] WHERE ',' + talents + ',' LIKE '%,#aTalent#,%' Tyler Clendenin GSL Solutions -----Original Message----- From: Bryan Love [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 1:20 PM To: CF-Talk Subject: RE: SQL : Finding element in list of values in a query field. First of all the DB design is incorrect for what you are trying to do... That being said, I've had to deal with this before and here is the best possible solution without changing the DB... 1) modify the GUI you use for data entry so that the talents list is inserted into the database with a comma at the beginning and at the end. So a list of talents looks like this in the DB: ",running,dancing,cooking," 2) run a query that will modify the data that is already in the DB by adding a comma before and after the talents string: UPDATE [table] SET talents = ',' + talents + ',' WHERE talents IS NOT NULL AND talents <> '' 3) now you can run queries like this: SELECT * FROM [table] WHERE talents LIKE '%,#aTalent#,%' +-----------------------------------------------+ Bryan Love Database Analyst Macromedia Certified Professional Internet Application Developer TeleCommunication Systems [EMAIL PROTECTED] +-----------------------------------------------+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis "Let's Roll" - Todd Beamer, Flight 93 -----Original Message----- From: Angel Stewart [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 10:34 AM To: CF-Talk Subject: SQL : Finding element in list of values in a query field. Hey all, Say for example I have a table: Firstname Lastname Address Talents. Talents for one record is: acting,dancing,singing. For another record it is : dancing,singing,music I want to construct a query to find all those with dancing as a talent. How would I do that using SQL...*can* I do that using SQL? -Angel ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 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