ISO Subdivision | ISO_SUBDIVISION

Syntax

ISO_SUBDIVISION( [value], [country], [returnType = "CODE"] )

Parameters

value
The input TEXT that will be converted to or from a subdivision code. If it is not a TEXT value, an empty TEXT value will be returned.

country
The input TEXT that will be converted to or from a country code. The country code is required as subdivision codes are not unique across different countries. If it is not a TEXT value, an empty TEXT value will be returned.

returnType optional
Determines whether or not to convert from or to a subdivision code. "CODE" will convert the input to a subdivision code. "NAME" will return the full subdivision name from a subdivision code. Defaults to "CODE" if omitted.

Return Value

A TEXT value. Will be the abbreviated subdivision code when returnType is set to "CODE". Will be the full subdivision name when returnType is set to "NAME". Will always return an empty TEXT value if either the country or the subdivision value is not found.

Description

Takes a TEXT input and converts it into the standard subdivision code abbreviation. A subdivision code represents the geopolitical ara designations of a country. Depending on the country, this may include states, provinces, counties, etc. Can also be used to validate subdivision codes or subdivision names.

Examples

Convert a state name into an ISO code.

ISO_SUBDIVISION("Texas", "US")
# returns "TX"

The country value is required

ISO_SUBDIVISION("Texas")
# returns "" because the country code was omitted

Works with the country name

ISO_SUBDIVISION("Texas", "United States")
# returns "TX"

Check to see if a subdivision code is valid for a specific country

ISO_SUBDIVISION("TX", "US")
# returns "TX"
ISO_SUBDIVISION("XX", "US")
#returns "" because there is no "XX" subdivision code for the United States.

Get the full subdivision name from the subdivision code.

ISO_SUBDIVISION("TX", "US", "NAME")
# returns "Texas"

Returns an empty TEXT value if either the value of VALUE or COUNTRY is a non string value or is omitted

ISO_SUBDIVISION(null, null)
# returns ""
ISO_SUBDIVISION("Texas", null)
# returns ""
ISO_SUBDIVISION(null, "US")
# returns ""
ISO_SUBDIVISION(20, "US")
# returns ""

Handles 3 character country codes

ISO_SUBDIVISION("Texas", "USA")
# returns "TX"

Handles 3 character subdivision codes

ISO_SUBDIVISION("North Yorkshire", "GB")
# returns "NYK"
ISO_SUBDIVISION("NYK", "GB", "NAME")
# returns "North Yorkshire"