Post reply

Data Type for fixed-length number?

  • April 27, 2010 at 5:27 pm

    #220928

    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).

  • April 28, 2010 at 12:50 am

    #1158662

    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!

  • April 28, 2010 at 10:00 am

    #1159092

    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!

  • April 28, 2010 at 10:03 am

    #1159096

    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!

  • April 28, 2010 at 10:13 am

    #1159107

    Thank you ColdCoffee and Lowell! This is exactly what I was looking for. 😀

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply