OK
To simplify things let's say the search variable is named LookingFor
and contains a single value (10)
the rccords in the db contain:
# SearchField
--- -----------
1 1,2,3,4,9,100
2 210,310,410
3 10,11,12
4 8,9,10
So, the query is:
... WHERE
SearchField = '10' -- Match Only Element of field
OR SearchField LIKE '10,%' -- Match Beginning Element of Field
OR SearchField LIKE '%,10,%' -- Match Middle Element of Field
OR SearchField LIKE '%,10' -- Match Last Element of Field
OK, If that works, then if CF you must build a similar set of 4 tests
for each list element in the LookingFor variable, by looping over it
as a list:
If yoy have a value in LookingFor of (1,10,100), cour CF-generated
statement would look like this:
... WHERE
SearchField = '0' -- Match Only Element of field
OR SearchField LIKE '1,%' -- Match Beginning Element of Field
OR SearchField LIKE '%,1,%, -- Match Middle Element of Field
OR SearchField LIKE '%,1' -- Match Last Element of Field
OR SearchField = '10' -- Match Only Element of field
OR SearchField LIKE '10,%' -- Match Beginning Element of Field
OR SearchField LIKE '%,10,%' -- Match Middle Element of Field
OR SearchField LIKE '%,10' -- Match Last Element of Field
OR SearchField = '100' -- Match Only Element of field
OR SearchField LIKE '100,%' -- Match Beginning Element of Field
OR SearchField LIKE '%,100,%' -- Match Middle Element of Field
OR SearchField LIKE '%,100' -- Match Last Element of Field
Now, all this garbage is the penalty you pay, again and again, for
not having a normalized db... SearchField is not atomic.
What I would suggest is that you add another child table to contain 1
record for each SearchField element.
The search will be simple and fast (done entirely in the indexes).
You can keep the composite field around if needed for other purposed
(denormalized fields are acceptable if you use them as information
fields & don't need to search on them...
This would would be a similar, practical, denormalization as carring
the total in the order record, rather than computing the sum of all
the line items.
HTH
Dick
At 1:55 PM -0400 6/13/01, Paul Sinclair wrote:
>Dylan,
>
>Thanks for this suggestion. This doesn't do what is needed though I don't
>think. My trouble is I am comparing one list of values against another list
>of values. Take the following situation:
>
>1. The string I want to compare against is this: 1,3,5
>
>2. The field (FLD) I am comparing it against contains this value: 2,4,10
>
>If I use IN, it would look like this:
>
>WHERE '2,4,10' IN('1,3,5')
>
>What I _WANT_ to do is this (in layman's terms):
>
>get all records where (1 = 2 or 1 = 4 or 1 = 10) OR (3 = 2 or 3 = 4 or 3 =
>10) OR (5 = 2 or 5 = 4 or 5 = 10)
>
>I'm comparing discrete values in the comparison list against discrete values
>in the the field value list. Only if one of the discrete values matches do I
>want to the record.
>
>Any ideas on how to accomplish this?
>
>Thanks for ideas!
>
>Paul Sinclair
>
>
>> -----Original Message-----
>> From: Dylan Bromby [mailto:[EMAIL PROTECTED]]
>> Sent: Wednesday, June 13, 2001 11:01 AM
>> To: CF-Talk
>> Subject: RE: Comparing value against a list of values
>>
>>
>> Paul,
>>
>> You should use the IN operator in your WHERE clause. I'm not clear on your
>> data structure from your post, but try something like the following (make
>> sure the list is in parentheses);
>>
>> SELECT
>> *
>> FROM
>> tbl
>> WHERE
>> csvList IN (10,11,21,100)
>>
>> -----Original Message-----
>> From: Paul Sinclair [mailto:[EMAIL PROTECTED]]
>> Sent: Wednesday, June 13, 2001 7:17 AM
>> To: CF-Talk
>> Subject: SQL: Comparing value against a list of values
>>
>>
>> I have a table with a field that contains a comma delimited list
>> of values,
>> such as:
> >
>> 10,11,21,100
>>
>> In doing a <cfquery> against the table, I want to select records where one
>> or more of the values in this list of values matches a value in the WHERE
>> clause of my sql query. For example:
>>
>> select *
>> from tbl
>> where csvList = '%1%'
>>
>> The problem is that the above select statement will select ANY record that
>> has '1' in the field no matter what the "true" values in the field are
>> (i.e., 1 is "LIKE" 10,11,21,100).
>>
>> How do I make the select query compare '1' against '10', and '1' against
>> '11' and '1' against '21', and '1' against '100'.
>>
>> Thank you for help.
>>
>> Regards,
>> Paul Sinclair
>>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
