MeridianMERIDIAN

State/Province Code

geography.location.state_code

Two- or three-letter state or province abbreviation. Locale-specific due to country-dependent subdivision codes (e.g., US states, CA provinces, AU states/territories). The US form is the USPS abbreviation; the AU form is the ISO 3166-2:AU subdivision code without its country prefix (AU-NSW is the ISO 3166-2 form of NSW).

Try a value

State/Province Code's rule accepts it.

Reads it as CA

Also accepted by

Casts to
VARCHAR
Scope
Locale-specific
Standard
Locales
EN_US, EN_CA, EN_AU
Per-locale rule
EN_AU8 values, e.g. ACT, NSW, NT
EN_CA13 values, e.g. AB, BC, MB
EN_US62 values, e.g. AL, AK, AZ

Shape

Pattern
^[A-Z]{2,3}$
ValueLengthShape
CA2matches
NY2matches
TX2matches
FL2matches
ON2matches
NSW3matches
C1rejected: one character short

Told apart

Going the other way, this type's own rule accepts 5 of 5 samples of Country Code.

FM, MH, PW and AE are also ISO 3166-1 country codes, so a bare column of them is ambiguous between the two types (geo_code_membership_vote leaves such a column alone).

Background

Origin

The modern two-letter codes for US states and territories originated in October 1963 with Publication 59.[1] The US codes arrived three months after ZIP codes were introduced in July 1963.[1] Only one US state code has changed since 1963: Nebraska moved from NB to NE in November 1969 to avoid confusion with Canada's New Brunswick.[1]

Canada Post's two-letter provincial and territorial abbreviations replaced the inconsistent traditional system that Canadians used until the 1990s.[2] In 1991, the Canadian code for Quebec was changed from PQ to QC.[2] On 21 October 2002, the postal abbreviation for Newfoundland and Labrador was changed from NF to NL.[2] Before the Nunavut code NU took effect in December 2000, NT covered both Nunavut and the Northwest Territories.[2] Canada avoided abbreviations that overlap with US state codes, which is why NV was not used for Nunavut.[2]

ISO 3166-2, published by the International Organization for Standardization and first published in 1998, defines codes for the principal subdivisions of countries.[3] For Canada, ISO adopted the existing Canada Post abbreviations.[2]

Use

The US Census Bureau's code list gives each state both a numeric State FIPS code and a state postal abbreviation.[4]

Sources

  1. [1]List of U.S. state and territory abbreviationssecondary source
  2. [2]Canadian postal abbreviations for provinces and territoriessecondary source
  3. [3]ISO 3166-2secondary source
  4. [4]American National Standards Institute (ANSI), Federal Information Processing Series (FIPS), and Other Standardized Geographic Codesorganisation

Detection notes

New in v13. Separated from country_code remap (state_code→country_code was causing US state abbreviations like OR, CA, FL to train as country codes). Locale-specific: US (50 states + DC + territories, the freely associated states FM, MH and PW, and the military codes AA, AE and AP, as in USPS Publication 28 Appendix B), CA (13 provinces/territories), AU (8 states/territories, ISO 3166-2:AU). AU codes are 2-3 letters, so the universal pattern is ^[A-Z]{2,3}$. FM, MH, PW and AE are also ISO 3166-1 country codes, so a bare column of them is ambiguous between the two types (geo_code_membership_vote leaves such a column alone). For discrimination a value counts only as two uppercase letters or an EN_AU three-letter code, so three-letter airport and currency codes do not; the other three-letter forms the pattern accepts conform but are not counted.

Use it

CLI · DuckDB
$ finetype infer -i "CA" --mode column
→ geography.location.state_code

SELECT ft_infer('CA');
-- → 'geography.location.state_code'
Cast expression
UPPER(CAST({col} AS VARCHAR))

Schema

https://meridian.online/schemas/geography.location.state_code · Frictionless type string

finetype taxonomy geography.location.state_code -o json-schema

Aliases

state_abbreviationprovince_code

Type Registry