[DB2] How can I add a check to test the length of an integer input?

Question

... for strings, it's easy... LENGTH(attribute), but for ints, LENGTH always returns "4" for ints, "8" for bigints, etc etc.

So given a statement like this:

create table EMPLOYEE (employeeno bigint NOT NULL PRIMARY KEY);

How could I add a CHECK clause that'll check to see whether an inserted employeeno is exactly 6 digits?

Answer 1

Convert it or Cast it as varchar first then use length.

Answer 2

Use a check constraint to check for val >= 100000 and val <= 999999.

If you need to, you can change this to val >= 0 and val <= 999999. Note that int types do not contain any information about leading zeroes, so that information is already lost by the time it hits the database.

Answer 3

Personally, I would prefer to check integers using numeric constraints. So this begs the question, do you really want to look at the characlter length of the string representation of the integer? For example, should -100000 be allowed? I am assuming that is not the case, and suggest something like:

create table EMPLOYEE (
    employeeno integer
        CHECK (employeeno BETWEEN 1000000 AND 999999)
        NOT NULL PRIMARY KEY
);

Note I changed bigint to integer since the constraint necessarily prevents values from being added that are beyond the precision of an integer