Constraint Check for 10 Digit Character use for Postal Code

Question

I have a table with a Char(10) column type, named postal Code and I need a Constraint check for all values just be 10 digits like 1234567890 and nothing else, I use the following:

CONSTRAINT [CH_PCDigit] CHECK ( [PostalCode] LIKE '%[^0-9]%'),
CONSTRAINT [CH_PCLength] CHECK ( LEN([PostalCode])=10)

but not worked correctly, why? and what is your suggestion? is there any way to merge this 2 constraint with one?

And what about if I want a Postal Code like this: 12345-54321 mean: 5digit-5digit? (Also Type must be Char(11)).

Does any one know any good source for Rgex or Constraint Check in SQl?

Solution 1

SQL Server TSQL does not support full blown RegEx's. You can do what you want in a single constraint like so:

CONSTRAINT [CH_PCDigit] 
    CHECK ([PostalCode] LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

or better:

CONSTRAINT [CH_PCDigit] 
    CHECK ([PostalCode] LIKE REPLICATE('[0-9]', 10))

If you want to allow dashes:

CREATE table  ChkTest
(
    PostalCode char(10) not null
        CONSTRAINT [CH_PCDigit]  
    CHECK ([PostalCode] LIKE REPLICATE('[0-9,-]', 10)) 
)

-- Test Code...

insert into ChkTest 
select '1234567890'

insert into ChkTest 
select '123456780'

insert into ChkTest 
select '12345678y0'

insert into ChkTest 
select '12345678901'

select * from ChkTest

insert into ChkTest 
select '12345-8901'

Solution 2

Here is one that accepts both U.S. Zip Code and Canada Postal Code.

CONSTRAINT CH_PCDigit
CHECK (PostalCode LIKE '[0-9][0-9][0-9][0-9][0-9]' OR 
       PostalCode LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
       PostalCode LIKE '[A-Y][0-9][A-Z][0-9][A-Z][0-9]')

Solution 3

YOu can use isnumeric, split big number:

CREATE TABLE a (
pc CHAR(10),
CONSTRAINT pc_c CHECK ( 
       LEN(pc) = 10 AND 
       ISNUMERIC(SUBSTRING(pc,1,5))=1 AND 
       ISNUMERIC(SUBSTRING(pc,6,5))=1)
)