Digit restriction on SQL server numeric field

Question

How can i restrict field in a table to 15 or 16 digits. I have this table:

create table Person(    
    ,UserID varchar(30)
    ,Password varchar(30) not null
    ,CCtype varchar(8)
    ,CCNumber numeric
    ,primary key(UserID)
    ,constraint CK_CCvalidity check 
        (
            (CCType is null or CCNumber is null) 
            or 
            (
                (CCType = 'Amex' or CCType = 'Discover' or CCType = 'MC' or CCType = 'VISA')
                and
                (CCNumber >= 15 and CCNumber <= 16)
            )
        )   
);

But this actually checks for the values 15 an 16, not for the number of digits. Also, we can assume that the numeric may hold 000... as the first digits.

Thanks for the help

Not related to your question, but you might be better off with a one to many relationship between users and credit cards. Also, make sure you comply with all the relevant security guidelines wrt storing credit card data. — Dan Bracuk, Jan 21 '14 at 00:42

Mitch Wheat · Accepted Answer · 2014-01-21T00:08:20.337

CCNumber should never be numeric. That will lead to a world of pain.

It should be varchar(X) where X is 13 - 24 digits.

Credit card numbers are usually represented by groups of 4 or 5 digits separated by spaces or dashes or simply all together with no separators.

[note: American Express: 15 digits; Visa: 13 or 16 digits]

In response to your comment:

ALTER TABLE dbo.Person
    ADD CONSTRAINT CK_Person_CCNumber 
                   CHECK (LEN(CCNumber) = 16 OR LEN(CCNumber) = 15);

But probably better as:

ALTER TABLE dbo.Person
    ADD CONSTRAINT CK_Person_CCNumber 
                   CHECK (LEN(CCNumber) >= 13 AND LEN(CCNumber) <= 15);

AND add a constraint to ensure it is a valid credit card number perhaps (there are plenty of examples online).

Bank Card Number

Ok thanks for the response, but even if I do varchar, how would I check if the length of the field is 15 or 16 numeric characters? — setlio, Jan 20 '14 at 23:58

score 0 · Answer 2 · edited May 23 '17 at 11:49

You can create a function to remove the Non-Numeric characters from a varchar, like this one:

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

Now, if you want to allow only digits and want to check the length, you could add two Check Constraints like this:

Create Table Person
(
    Id int not null primary key,
    CCNumber varchar(30),

    CONSTRAINT CK_Person_CCNumber_Length CHECK (LEN(CCNumber) BETWEEN 15 AND 16),
    CONSTRAINT CK_Person_CCNumber_IsNumeric CHECK (LEN(dbo.[fnRemoveNonNumericCharacters](CCNumber)) = LEN(CCNumber))
)

First Constraint will check the length of the field to be 15 or 16.

Second one will check that the field is numeric (length of field removing non-numeric is equal to length of the original field)

You can also do it in just one ANDed Check Constraint.

score 0 · Answer 3 · answered Jan 21 '14 at 02:31

Storing a credit card number as a...number is guaranteed to shoot you in the foot some day. Such as the day you start encountering credit card numbers with leading zeroes. They may consist of decimal digits, but they're not numbers. They're text.

Plan for the future: what happens when somebody start issuing credit card numbers with letters?

So, try this:

create table dbo.some_table
(
  ...
  credit_card_type   varchar(8)  null ,
  credit_card_number varchar(32) null ,

  constraint some_table_ck01 check (
       (     credit_card_type   is not null
         and credit_card_number is not null
       )
    OR (     credit_card_type   is     null
         and credit_card_number is     null
       )
    ) ,
  constraint some_table_ck02 check (
    credit_card_type in ( 'amex' , 'discover' , 'mc' , 'visa' )
    ) ,
  constraint some_table_ck03 check (
    credit_card_number not like '%[^0-9]%'
    ) ,
  constraint some_table_ck04 check (
    len(credit_card_number) = case credit_card_type
                              when 'amex'     then 15
                              when 'discover' then 16
                              when 'mc'       then 16
                              when 'visa'     then 16
                              else                 -1 -- coerce failure on invalid/unknown type
                              end
    ) ,
) 
go

insert some_table values( null   , null              ) -- succeeds
insert some_table values( 'amex' , null              ) -- violates check constraint #1
insert some_table values( null   , '1'               ) -- violates check constraint #1
insert some_table values( 'acme' , '1'               ) -- violates check constraint #2
insert some_table values( 'amex' , 'A1B2'            ) -- violates check constraint #3
insert some_table values( 'amex' , '12345'           ) -- violates check constraint #4
insert some_table values( 'amex' , '123456789012345' ) -- success!
go

But as noted by others, you need to fix your data model. A credit card is a separate entity from a customer. It has a dependent relationship upon the customer (the card's existence is predicated upon the existence of the customer who own it). You can a data model like the following. This

create table credit_card_type
(
  int         id          not null primary key clustered ,
  description varchar(32) not null unique ,
  ... -- other columns describing validation rules here
)

create table credit_card
(
  customer_id        int         not null ,
  type               int         not null ,
  number             varchar(32) not null ,
  expiry_date        date        not null ,

  primary key ( customer_id , number , type , expiry_date ) ,
  unique      ( number      , customer_id , type , expiry_date ) ,

  foreign key customer references customer(id) ,
  foreign key type references credit_card_type(id) ,

)

Further: you are encrypting card numbers using strong encryption, aren't you?

Digit restriction on SQL server numeric field

3 Answers3