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'
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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