sqlstandardsstateisocountry

Column type and size for international country subdivisions (states, provinces, territories etc)

I apologize if this is a duplication.

What column standardization would you use for storing international country subdivision data?

For example, if it was just US and Canada I believe all subdivisions have a 2-character abbreviation... which might lend to a Char(2)

This cannot possibly be sustainable internationally lest we presume there are only 1296 (A-Z, 0-9) subdivisions.

I've been unsuccessful locating an ISO list of these or even an indication of how to store them.

That's fine, I don't need to know them all now but I would like to know that there is a standard and what standard info to store as needed.

Thanks

EDIT: It appears that I can accomplish this using the ISO 3166-2 standard: http://en.wikipedia.org/wiki/ISO_3166-2

Browsable as a dataset here: http://www.commondatahub.com/live/geography/state_province_region/iso_3166_2_state_codes

Solution

  • As far as I know there are no international standards because it's a national issue

    Take the UK...

    Your question arguably assumes a federal structure (as per Switzerland where I am) but this won't apply to many if most countries. Carrying on with Switzerland, Kanton does not feature in postal addresses or post codes either.

    If there is an ISO standard, then national or local pride will annoy punters as soon as it's on your web site.

    Personally, I dislike wading through a "state" dropdown on a web site. It has no meaning for me in either UK (my nationality) or my residence (Switzerland).

    You may be best to stick states from US and Canada and "non US/Canada". Don't force or assume a sub-division.

    Edit, Jun 2012.

    I now live in Malta. I have neither state, county, nor Kanton. Please don't insist.

    Any big cities in the UK don't normally mention county (England+Wales)/region (Scotland).