CODEWITHSUNDEEP
sqloracle-database
Shannon Severance
Shannon Severance

Reputation: 18410

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

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 since 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'

Upvotes: 14

Views: 38094

Answers (8)

danny
danny

Reputation: 71

in MS SQL server I use this command: alter table add constraint [cc_mytable_myfield] check (cast(myfield as bigint) > 0)

Upvotes: 0

Rob van Wijk
Rob van Wijk

Reputation: 17705

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.

Upvotes: 11

Theo
Theo

Reputation: 819

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

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52396

Works in all versions:

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

Upvotes: 8

Tim Sylvester
Tim Sylvester

Reputation: 23168

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

Upvotes: 17

blispr
blispr

Reputation: 912

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

Upvotes: -2

richardtallent
richardtallent

Reputation: 35404

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.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

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

Upvotes: 0

Related Questions