Easy question concerning multiple values and lists

NettlesD wrote:
> ... I could use to put the values in some type of workable format for the IN clause. That's all
> I'm after or point me to some function(s) that might pull this off.
>

The trouble there is NO workable format for the IN clause because an IN
clause will not work.

An IN clause says find all records that contain a match to any of the
values IN this list.

You want to find all records where a list value may contain a given string.

There is no help for this beyond the use of the LIKE operator or some
other database string matching utility, such as regex.

Whatever your solution is, it is going to be in the Database and
ColdFusion just ain't going to help much.

Unless you are willing to retrieve the entire table into ColdFusion and
then process it there to filter out the desired records. That would
allow you to use the listContain() and other list functions. But this
would be a poor performing and very un-scaleable solution.

I totally agree but I didn't set up this database or write the code that inputs these values. I'm just the one who is trying to work out a solution with limited Cold Fusion knowledge.

I would think there would be some CF function or something that I could use to put the values in some type of workable format for the IN clause. That's all I'm after or point me to some function(s) that might pull this off.

NettlesD wrote:
> This should be easy for someone.
>
> If I have a column in a database that has a value of 463,241 (the numbers 463
> and 241) and these numbers are input to a query, how can I make these number
> like "select first_name from faculty where faculty_id in (463, 241)"
>
> Is there a way to convert the numbers in that column to something like "463",
> "241" so that they can be used in a Where IN clause? I tried ListAppend but
> that didn't seem to work. Some type of list function so that the query
> correctly processes the values?
>


NOT an easy answer though. You are now suffering the consequences of
not having a normalized database design.

To do a select with that type of data you are going to have to use
string matching it is going to look something like this:

WHERE id LIKE '%463,' OR id LIKE '%,463,%' OR id LIKE '%,463' OR id =
'463' ...

If you had a properly normalized database where you did NOT have a
column with a list of values, but rather had a join table between a
table of faculty and whatever is in this table then you could do a easy
join query such as this.

FROM thisTable INNER JOIN facultyJoinTbl ON thisTable.facKey =
facultyJoinTbl.facKey

WHERE facultyJoinTbl.id= 463