SQL Server – Check Constraint for Canadian Postal Code

check-constraintssql server

Is there a way to check if a Canadian postal code is valid? Not sure if something like this would work:

PostalCode varchar(10) CHECK(PostalCode>='t1w1v1' and PostalCode<='t9w9v9'

Best Answer

Canadian postcodes are

in the format A1A 1A1, where A is a letter and 1 is a digit, with a space separating the third and fourth characters ... Postal codes do not include the letters D, F, I, O, Q or U, and the first position also does not make use of the letters W or Z.

So the following should do it.

CHECK (PostCode LIKE REPLACE(REPLACE('Alpha1[0-9]Alpha2 [0-9]Alpha2[0-9]', 
                                     'Alpha1', 
                                     '[ABCEGHJKLMNPRSTVXY]'), 
                             'Alpha2', 
                             '[ABCEGHJKLMNPRSTVWXYZ]') COLLATE Latin1_General_Bin) )

Also you should probably use CHAR(7) if you are only allowing values exactly 7 characters long (especially if this column is mandatory).

As the space appears predictably between the third and fourth characters arguably storing this is redundant and it should be added at display time instead. If you decide to go that route then use CHAR(6) and remove the space in the middle of 'Alpha1[0-9]Alpha2 [0-9]Alpha2[0-9]'

Related Question