Use SQL to find string within database field

I have the following values stored in a comma delimited list... for example

list: 13,14,16,18

there is a url variable #fc# that i need to find in the database field containing this list (named teams_selected).  I tried to write a query to do as described but it doesn't seem to be working.

<cfquery>select * from teams_schedule_new where '#fc#' in (teams_selected) order by id desc</cfquery>

Your help is appreciated.
LVL 3
EduskiAsked:
Who is Participating?
 
Jones911Connect With a Mentor Commented:
eams_selected  in the 2nd last line needs to be teams_selected
0
 
Jones911Commented:
what is teams_selected ?
0
 
EduskiAuthor Commented:
it is the database field that contains the list of comma separated items

value is "13,14,16,18"
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Jones911Commented:
is fc a integer?
0
 
EduskiAuthor Commented:
doesn't have to be.  the query as written just returns no records if i test it
0
 
Arthur_WoodCommented:
The problem is that the variable "teams_selected" is a string, NOT a comma delimited list of integers.  In order to use the IN construct, you need a COMMA DELIMITED list of values - you need to break the variable teams_selected into its set of separate values, as a collection (the separate values 13, 14, 16 and 18 as individual values.

check out

http://www.sqlbook.com/SQL/Parse-a-comma-delimited-list-8.aspx

which does precisely what you need.

AW
0
 
Jones911Commented:
Author states teams_selected is a list.

Author is teams_seletced meant to be in ## so as to be evaluated as a variable?
0
 
Arthur_WoodCommented:
you can also use BING, and query on

sql parse comma delimited string

AW
0
 
EduskiAuthor Commented:
Arthur is right.  teams_selected is right now a string, which is where I was going wrong.  Since I can't understand the link Arthur posted, can someone tell me how to convert the string value to a list so I can properly search it?
0
 
EduskiAuthor Commented:
Sample code is appreciated, I'm not good at SQL.
0
 
Jones911Commented:
select * from teams_schedule_new where teams_selected like '%,#fc#,%' order by id desc

should do the same thing
0
 
EduskiAuthor Commented:
yeah but the last number isn't followed by a comma
0
 
EduskiAuthor Commented:
and the first isn't preceeded by a comma either
0
 
Jones911Commented:
select *
from teams_schedule_new
where teams_selected like '%,#fc#,%'
or ','+teams_selected like '%,#fc#,%'
or teams_selected+',' like '%,#fc#,%'
order by id desc
0
 
EduskiAuthor Commented:
your last suggestion eliminates records where '13' is the only entry without commas.
0
 
Jones911Commented:
select *
from teams_schedule_new
where teams_selected like '%,#fc#,%'
or ','+teams_selected like '%,#fc#,%'
or teams_selected+',' like '%,#fc#,%'
or eams_selected = '#fc#'
order by id desc
0
 
EduskiAuthor Commented:
its not pretty but it works, thanks.
0
 
Jones911Commented:
Yeah.  Gotta do what you gotta do when the database is not designed right though :)  Glad it worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.