14

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'

Improve this question
2
  • +1 for sparking a good philosophical debate ;-) Commented Jul 23, 2009 at 20:30
  • +1 for the observation that if arithmetic operations do not make sense on the column then it should not be stored as a number. It's been an irritation to me for years that people will store an SSN or phone number or customer account number in a numeric data type, and then will need TO_CHAR() on it in order to handle it properly. Well done for that. Commented Dec 18, 2012 at 9:11

8 Answers 8

Reset to default
17 
REGEXP_LIKE(PROD_NUM, '^[[:digit:]]{9}$')
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Why the two sets of square brackets around :digit: instead of just one set.
One set indicates a character class, two indicates a named character class. See en.wikipedia.org/wiki/…
One small caveat: REGEXP_LIKE doesn't make use of indexes like a LIKE operator would. This is fine for the OP's problem (an INSERT/UPDATE constraint), but may not provide adequate performance when searching records after the fact.
@rihardtallent: indexes are never used by check constraints anyway.
11

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.

Improve this answer

19 Comments

@Rob, you raise many good points. However the world is filled with "numbers" that aren't numbers, but are instead codes or IDs. VINs (Vehicle Identification Numbers) and many serial numbers come to mind. The users have decided to call these product numbers and decided that they want them to be zero padded on the left. At some layer of the application I will have to have a product number that is a zero padded string. I would rather be consistant all the way to the base table.
Okay, one last comment and I'll shut up. I am currently working at a site where this exact scenario has played out. A centrally important "number" to our system started out being all numeric digits. Satellite systems began assuming numbers; so did the apps. Then, our customer says, "oh, BTW, we want you to prefix your "numbers" with two alpha chars. Guess what? Years down the road and we still feel the pain. ALWAYS LOOK DOWN THE ROAD. CONSIDER THE WORST CASE. Don't assume that because things look like numbers today that they must always be numbers. Externalities count!
Okay, please allow one last comment from me as well. It is my belief that one has to choose the datatype that constraints the data just right. Just because it is not mathematically manipulated doesn't mean it's not a number. I'm not going to calculate with my surrogate key numbers, not with my telephone numbers, and not with my Tour de France rider numbers. I still consider them numbers because that constraints them properly. I'll never advice declaring such columns VARCHAR2(N) (4000?) or even a CLOB, to be prepared for everything that might happen to them. I use a view layer for that.
@Rob, "the purpose of my answer is just to make you aware of possible pitfalls when choosing the CHAR(9) approach." And thank you for that. I wanted to show that I had considered the issues you raised. And I liked the answer enough to up-vote it.
+1 for everyone for disagreeing so nicely and discussing it like civilised persons. Hooray!
|
8

Works in all versions:

TRANSLATE(PROD_NUM,'123456789','000000000') = '000000000'
Improve this answer

Comments

1

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

Improve this answer

1 Comment

Similar but a little shorter: TRANSLATE( prod_num, 'A0123456789','A') IS NULL.
0

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

Improve this answer

1 Comment

(with leading zeros added back)
0

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.

Improve this answer

1 Comment

Oracle's LIKE operator does not support regular expressions.
0

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

Improve this answer

1 Comment

This question is answered before, obviously, you can add your answer here. But You Need to understand some points before answering. First, don't add an answer which is previously added with the same code or suggestion. Second, don't add an overly complicated answer if the user has asked very specifically about the problem and what he needs to solve this. Third, You can add a comment if you want to suggest anything regarding the answer or question.
-2

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  );
Improve this answer

1 Comment

-1, this fails the OP requirement that numeric digits only are stored in the column, e.g., "insert into test values ('1.1');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.