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
