sql "ranges" and wildcards

Question

Hi.

Complete sql noob here.

Trying to think about a way to store some data in a table that looks a little like this:

"0-15", "low", "1-3"

"45-50", "med", "*"

"*", "*", "1000"

where column 1 & 3 are ranges of integers, and col 2 is an enumerated type (could be stored as whatever really).

i want to be able to put in wildcards in the table to keep the number of rows down while still not "missing" anything.

from what i understand it, column 1 & 3 would be best stored as two columns of integers each, with -INT_MAX to INT_MAX or whatever as a "wildcard". (select bla from bla where col1.1 > val and col1.2 < val)

is this a reasonable strategy?

column 2 seems trickier, select bla from bla where col2 = 'med' or col2 ='*' doesn't seem right. i could probably do these numerically just like 1 & 3 but would rather not

anyone care to enlighten me?

Answer 1

My preference would be to use NULL as the wildcard, since it will stand out prominently to any human studying the database. In that case, each element of your WHERE clauses will look something like this:

WHERE (77777 > min_value OR min_value IS NULL)
  AND (77777 < max_value OR max_value IS NULL)
  AND (col2 = 'med' OR col2 IS NULL)

This is the same basic idea of the * you had before, but using a value that SQL recognizes as being different from ordinary values.

Answer 2

A natural thing to do, given SQL, would be to use NULL as your wildcard value.