Here is a macro (sql function) we use for Canadian Postal
Codes. Bear in mind that the data we receive does not have the
normal space between the 3rd and 4th characters. The percentage
sign represents the argument.
returns Y or N to indicate whether postal code is valid CASE
WHEN
SUBSTR(%1,1,1)IN('A','B','C','E','G','H','J','K','L','M','N','P','R','S','T','V','X','Y')AND
SUBSTR(%1,2,1)IN('0','1','2','3','4','5','6','7','
8','9')AND
SUBSTR(%1,3,1)IN('A','B','C','E','G','H','J','K','L','M','N','P','R','S','T','V','W','X','Y','Z')AND
SUBSTR(%1,4,1)IN('0','1','2','3
','4','5','6','7','8','9')AND
SUBSTR(%1,5,1)IN('A','B','C','E','G','H','J','K','L','M','N','P','R','S','T','V','W','X','Y','Z')AND
SUBSTR(%1,6,
1)IN('0','1','2','3','4','5','6','7','8','9')THEN 'Y' ELSE
'N' END