CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstorage
Analytic Lunatic
Analytic Lunatic

Reputation: 3944

How to capture data values to the power of ^ (...) in SQL Server?

Perhaps someone with more experience in SQL Server can be of assistance. I am in the middle of putting together the LookUp tables for a new project. For 2 different tests that a user can perform (Bacteria/Fungi) the results are currently recorded on paper as the following:

BACTERIA: Bacteria cfu / ml

FUNGI: Fungi (yeast & mold) cfu /ml

Upvotes: 3

Views: 1280

Answers (3)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Something like this would probably be good enough:

CREATE TABLE AmountLookup (
  UnitsLimitExp int NULL,
  Name nvarchar(10) NULL
)

INSERT INTO AmountLookup
SELECT 2, '<100'
UNION ALL SELECT 3, '10^3'
UNION ALL SELECT 4, '10^4'
UNION ALL SELECT 5, '10^5'
UNION ALL SELECT 6, '10^6'
UNION ALL SELECT 7, '10^7'

This way you store the exponent, not the amount. Real value is just a GUI representation. Another thing is your lookup name, which is ugly here (10^3). However, you can store HTML code and treat it as raw HTML on your user interface, e.g. 104 is 10<sup>4</sup>.

Upvotes: 1

Andomar
Andomar

Reputation: 238116

I'd propose a varchar(50):

  • <100 "Clean enough"
  • 10^2 "Food over due date"
  • 10^3 "Mr Bean's armpits"
  • 10^4 "Three day old carcass"
  • 10^5 "Biochemical experiment"
  • 10^6 "Maggot invasion"
  • 10^7 "Bacteria overflow"

Upvotes: 0

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74390

If these are value and not ranges, INT will work. If you start to deal with values greater than 2 billion, BIGINT should be used. If you need decimal digits, you can use the DECIMAL (NUMERIC) type.

Your other option, since these are discrete values, is to use a lookup table for the values, whose surrogate key id you can use in the tables that hold references to the data. That way you can represent a concept such as "<100" .

Upvotes: 0

Related Questions