In a right state (province, territory...)

It's doubtful you'll ever need this, because I think most of us are smart enough to create an enumerated 'country' field for any database that will have to store international addresses. I find myself working with something (that I'm not allowed to change) where U.S, Canadian and possibly other addresses have all been shoved in the same half–assed table. I knocked up the following to help unpick at least the .us and .ca addresses based on the contents of the 'state' field. It's by no means perfect: I've seen at least one record where an address line says 'LONDON / ONTARIO' and the state field contains 'CAN'. I don't think it's practical to test for every possible perversion though.

CASE WHEN al.ALADDS IN ('AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT',
  'DC', 'DE', 'FL', 'GA', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA',
  'MA', 'MD', 'ME', 'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE',
  'NH', 'NJ', 'NM', 'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC',
  'SD', 'TN', 'TX', 'UT', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY' )
  THEN
    'US'
  ELSE
    CASE WHEN al.ALADDS IN ('AB', 'BC', 'MB', 'NB', 'NL', 'NS', 'NT',
      'NU', 'ON', 'PE', 'QC', 'SK', 'YT')
    THEN
      'CA'
    ELSE
      ''
  END
END

Now on to the postcodes and zip codes, which are all mashed into the same alphanumeric field. :-/