SQL select troubles... bad db design?

[demon42]

The more I think about it, the more I think my design is bad and that I should re-work the db... but I'd rather not if I can avoid it.

My database is storing a list of classes, like what a university would have. CRN, title, days of the week, etc. I am implementing the db through a Java application, many methods would have to be re-writtenif I needed to re-structure the db (poor planning on my part, I guess).

I decided to store days of the week as an integer, +64 for mon, +32 for tue, +16 for wed, etc. but now I have a problem searching! I can take a search string "w" and change it into a 16, no problem. The question is how do I match that against the number 84 ("mwf")?

My SELECT (much simplified) is like this:

sqlQuery = String.format("SELECT * FROM CLASS WHERE Day LIKE '%%%s%%'", filters[0]);

It's really much more complicated than that, but it should be enough to get my meaning.

I'm really struggling with this, hoping not to re-work the db. Any ideas?

 

[Argo]

There are bitmas operators in sql that you could use. I don't remember the exact syntax, but I'm pretty sure every sql reference would have it.

 

[demon42]

hmm, i'm not quite sure how to do that... but i'll try to find out!

Thanks for your help!

 

[demon42]

Got it!
sqlQuery=String.format("SELECT * FROM CLASS WHERE DAY & '%s' = '%s'", filters[0], filters[0])

where filters are a string representation of the value in question, aka for wednesday, "16"

Thanks again for putting me on the right track!