The suggestion Brian made about adding commas around the values is actually a VERY good suggestion. Here's why.
With string items such as "dancing" or "running", you're probably okay without the commas, but what if your table looked like this: NAME | LUCKY NUMBERS --------------------- bob | "1,3,15,26" susan | "1,4,17,26" joe | "2,6,14,28" To get a list of a people whose lucky numbers are "7", you'd run a query that looked like this: SELECT name FROM luckyNums WHERE luckynumbers LIKE '%7%' Your query returned "susan" which is incorrect. However, if we reformed the table to look like this: NAME | LUCKY NUMBERS --------------------- bob | ",1,3,15,26," susan | ",1,4,17,26," joe | ",2,6,14,28," And altered the query to look like so: SELECT name FROM luckyNums WHERE luckynumbers LIKE '%,7,%' Then your query returned ZERO rows, which *IS* correct. And THAT'S why Brian was suggesting adding the extra commas. They ARE VERY important, depending on what type of data you have. For the record, I agree that the CORRECT solution is to create another table and a linking table. -----Original Message----- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 2: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 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
