DEV Community

Syed Saadullah Shah
Syed Saadullah Shah

Posted on

MySQL table for Australian States with ISO code

Here is SQL for creating a table of Australian states. I previously published code for Canadian provinces and American States, but this one is slightly different. The official ISO codes for the abbreviation of Australian states are not the same ones commonly used in mailing addresses, so this table differs from the other two in that it has an extra “postal” column.

CREATE TABLE geo_states_au (
id INT(11) NOT NULL AUTO_INCREMENT,
printable_name CHAR(40) COLLATE utf8_unicode_ci NOT NULL,
iso CHAR(6) COLLATE utf8_unicode_ci NOT NULL,
postal CHAR(3) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_c;

INSERT INTO geo_states_au (id,printable_name,iso,postal)
VALUES
(1,'New South Wales','AU-NSW','NSW'),
(2,'Queensland','AU-QLD','QLD'),
(3,'South Australia','AU-SA','SA'),
(4,'Tasmania','AU-TAS','TAS'),
(5,'Victoria','AU-VIC','VIC'),
(6,'Western Australia','AU-WA','WA'),
(7,'Australian Capital Territory','AU-ACT','ACT'),
(8,'Northern Territory','AU-NT','NT');

Discussion (0)