How to store "smaller than", equals and "larger than" in database

Question

I need to store scientific information in a database (sql server). What is the best way to store a value in a database where "smaller than", larger than" is part of the information.

Example:

PatientId: 123 Mutation rate: <3%

PatientId: 999 Mutation rate: 3%

PatientId: 456 Mutation rate: 10%

I need to be able to sort and filter the data. A mutation rate of <3% is better than 3%

How can I sove this?

Thank you for your help

Answer 1

How about adding a 3rd column for a clarifying int?

0 = Less than
1 = Less than or equal to
2 = Equal to
3 = Greater than or equal to
4 = Greater than

Answer 2

The easiest, is typically to use predefined values for these cases, for example here, a value of 3.0 means 3% whereby 2.99 means "less than 3%".

Since these "Less than" and "More than" values typically only apply at the ends of the range, such a convention allows handling all filtering and ordering with a single field value,in a standard fashion. The main drawback to this approach is that it implies hard-coding these limit values at the level of the application, for display purposes and such.

The alternative is to introduce a two columns value, with a numeric value and a "qualifier" column which contains a code indicating "exact value" or "Less than" or "more than". While this approach appears to be more generic (and it is, at the level of the display etc.), some hard-coding is often required at the level of the "input".