Data Type for fixed-length number?
-
What would be the best data type for a fixed-length number? For example, a code that is always a 3-digit integer?
I had it set to integer, but then that doesn't prevent a larger number (like '4355' from being used).
-
You cant control it that way for INT data-types... Do this instead - Make the column INT, create a CHECK CONSTRAINT on that column to allow only integers from 0 to 999.. This will do the trick for you..
Cheers!
-
Thank you for the tip on Check Constraint. That will work, except should I consider a different datatype if the data is a 3-digit number, including leading zeros? The data would look like any of these: 000, 052, 020, 546, 600, etc.
Thanks, again!
-
000, 052, 020, <--those are not numbers. 0 is zero, there are no preceeding digits for real number types like integers, so you are right, you'd want to consider a different data type
if those are codes, then you might want to use a CHAR(3) datatype, but still have a constraint that makes sure all three characters are 0-9, no spaces allowed.
CHECK(mycol LIKE('[0-9][0-9][0-9]') ) i think is what you'd want.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you ColdCoffee and Lowell! This is exactly what I was looking for.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply