CHECK CONSTRAINT of string to contain only digits. (Oracle SQL)

Question

I have a column, say PROD_NUM that contains a 'number' that is left padded with zeros. For example 001004569. They are all nine characters long.

I do not use a numeric type because the normal operation on numbers do not make sense on these "numbers" (For example PROD_NUM * 2 does not make any sense.) And sense they are all the same length, the column is defined as a CHAR(9)

CREATE TABLE PRODUCT (
    PROD_NUM CHAR(9) NOT NULL
    -- ETC.
)

I would like to constrain PROD_NUM so it can only contain nine digits. No spaces, no other characters besides '0' through '9'

Answer 1

Cast it to integer, cast it back to varchar, and check that it equals the original string?

Answer 2

In MSSQL, I might use something like this as the constraint test:

PROD_NUM NOT LIKE '%[^0-9]%'

I'm not an Oracle person, but I don't think they support bracketed character lists.

Answer 3

Not sure about performance but if you know the range, the following will work. Uses a CHECK constraint at the time of creating the DDL.

alter table test add jz2 varchar2(4)
     check ( jz2 between 1 and 2000000 );

as will

alter table test add jz2 varchar2(4)
     check ( jz2 in (1,2,3)  );

this will also work

alter table test add jz2 varchar2(4)
         check ( jz2 > 0  );

Answer 4

REGEXP_LIKE(PROD_NUM, '^[[:digit:]]{9}$')

Answer 5

You already received some nice answers on how to continue on your current path. Please allow me to suggest a different path: use a number(9,0) datatype instead.

Reasons:

• You don't need an additional check constraint to confirm it contains a real number.

• You are not fooling the optimizer. For example, how many prod_num's are "BETWEEN '000000009' and '000000010'"? Lots of character strings fit in there. Whereas "prod_num between 9 and 10" obviously selects only two numbers. Cardinalities will be better, leading to better execution plans.

• You are not fooling future colleagues who have to maintain your code. Naming it "prod_num" will have them automatically assume it contains a number.

Your application can use lpad(to_char(prod_num),9,'0'), preferably exposed in a view.

Regards, Rob.

(update by MH) The comment thread has a discussion which nicely illustrates the various things to consider about this approach. If this topic is interesting you should read them.

Answer 6

I think Codebender's regexp will work fine but I suspect it is a bit slow.

You can do (untested)

replace(translate(prod_num,'0123456789','NNNNNNNNNN'),'N',null) is null

Answer 7

Works in all versions:

TRANSLATE(PROD_NUM,'123456789','000000000') = '000000000'