Need an expression to look up grade levels

[dmg2206]

I have an events table with a class field that I need to search. The class field contains a comma-delimited list of all the grade levels involved in event. For instance, if the event included just high schoolers it would be "12,11,10,9", or if it was even-numbered grades it would be "12,10,8,6,4,2".

The problem is trying to search this field for certain grades. Grades 3-12 are no problem (using INSTR()), but trying to search for all events that have, say, first graders is proving to be problematic. Searching for a '1' in the field will also return all that have 10th, 11th and 12th grades. And trying to search for rows that have "1" but not "10" will exclude rows which have both, and I need to include these.

Any ideas?

 

[vacunita]

This is the main reason having fields with comma delimited lists s not a good idea they are hard to search. You should make an additional table that has the relationship between grades and events.

event_ID grade_involved
1           10
1           7
1           3
2           8
3           2
3           4
3           6
4           10
4           8
4           4

Then you can search the table much more freely.

If of course modifying the db is not an option, perhaps , using your front end language of choice you can refine your searches. Though this may put an unnecessary strain on your system. As you are essentially retrieving rows that you'll maybe discard after your filter it further.


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[dmg2206]

The DB can be changed, but as this is a rather large application and such a change would require changing a whole lot of code. While the setup you describe would be better, it's not worth the time or money (I'm not working for free here) it would require.

Fortunately I seem to have figured out a solution. It helps that the application is set up to always list the grades in descending order.

For searching for 1st grade events, I have this:

`class` LIKE '%1' AND `class` NOT LIKE '%11'

Here to find all that end with "1" but not "11". A more elegant solution would have been to search for strings that have a "1" so long at it's not followed immediately by a "0", "1" or "2". But this version of MySQL does not seem to have that capability in its regular expression parser.

For searching for 2nd grade events:

`class` LIKE '2%' AND INSTR(`class`, ',2')

Finds every instance that either starts with "2" or contains a comma followed by a "2".

For every other class:

INSTR(`class`, '$grade')

where $grade is a variable containing the number of the grade.

Hope this helps someone.

 

[r937]

why not just use the FIND_IN_SET function

r937.com | rudy.ca