QUERYING A FIELD WITH MULTIPLE ENTRIES - HELP

[kingjjx]

Hi, I am trying to query a field with mutiple entries.
Its a level field and a person could be in level 1, 2, 3, 4 , and/or 5 the numbers are separated by commas

how do i query this field with coldfusion ?
i want to return any user that has a level of either 1,2,3,4,5

thank you
-j

 

[Silvano]

try using ListFind() or ListContains();

ListFind returns the index of the first occurrence of a value within a list. Returns 0 if no value is found. The search is case-sensitive.


Syntax: ListFind(list, value [, delimiters ])

list - List being searched

value - Number or string that is to be found in the items of the list

delimiters - Set of delimiters used in the list


Sylvano
dsylvano@hotmail.com

 

[GunJack]

Hey kingjjx,

Although I believe a different database structure would be best, I think something like this will do what you need.

select * from table where
level like '1,%' or level like '%,1' or level like '%,1,%' or level like '1'
or
level like '2,%' or level like '%,2' or level like '%,2,%' or level like '2'
or
level like '3,%' or level like '%,3' or level like '%,3,%' or level like '3'
or
level like '4,%' or level like '%,4' or level like '%,4,%' or level like '4'
or
level like '5,%' or level like '%,5' or level like '%,5,%' or level like '5'

Hope this helps,
GJ

 

[MorganGreylock]

Although checking for its existence in the SQL itself will work, its been my experience that the SQl statement is the big time-taker, so in that kind of situation, I would read the entire contents of the field into a variable, and then check it using ListContains... I have a similar field where people have entered 'Months Available', and can contain anything up to 1,2,3,4,5,6,7,8,9,10,11,12. When I want to display what months they are available, I do this:

<cfif ListContains(Months_Available, 1)>
	<td bgcolor=&quot;##ccccff&quot;><font face=&quot;Arial&quot; size=2 color=&quot;##000000&quot;>Jan</font></td>
<cfelse>
	<td> </td>
</cfif>

(In my case, CF has a nice 'MonthAsString' function that will turn 1 in to January, 2-> February, etc.)