4

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'
4
  • One gotcha I'm sure you are aware of here is many people assume the postal code has a space between the 3rd and 4th characters, like "R3L 1M7" - your user interface should be able to handle that. – Hannah Vernon Feb 8 '14 at 15:25
  • 3
    Canadian postal codes should have a space between the groups, and should use only upper-case letters. Whether or not it's stored in the database that way is a separate issue. See here: The Postal Code should be printed in upper case with the first three characters separated from the last three by one space. Do not use hyphens.. – Jon Seigel Feb 8 '14 at 17:26
  • @JonSeigel thank for the comment ok if change the REPLICATE to REPLICATE('[A-Z][0-9]',3) and make char 7 how can I support whitespace ? – NinjaDeveloper Feb 8 '14 at 18:57
  • 1
    If you want to store the space (and use only uppercase), you can use: LIKE '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]' – ypercubeᵀᴹ Feb 9 '14 at 10:28
8

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]'

3
  • +1 Martin, your constraint is readable, but it uses REPLACE to substitute a wide string [ABCEGHJKLMNPRSTVWXYZ] for its short placeholder. Have you benchmarked the performance implications: how faster it is without calling REPLACE twice? – A-K Feb 11 '14 at 15:48
  • @AlexKuznetsov - No. In a normal execution plan that would be subject to constant folding. So I took it that likely it will be for the algebrized trees stored for constraints too though. – Martin Smith Feb 11 '14 at 16:24
  • I don't understand why REPLACE is necessary here... why not just substitute the values into the LIKE clause? (i.e. ... LIKE '[ABCEGHJKLMNPRSTVXY][0-9][ABCEGHJKLMNPRSTVWXYZ] [0-9][ABCEGHJKLMNPRSTVWXYZ][0-9]' ...). – SQL_Deadwood Mar 10 '17 at 1:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.