I recently came across a very useful function that I am told was introduced in SQL Server 2017 called CONCAT_WS
I have never used it in any other application other than SQL Server I hope it exists in MYSQL and PostgreSQL
This will join together a series of fields with chosen separators and by combining it with NULLIF can be used to remove spaces and format address nicely.
For applications that will at some point need addresses either for post or for information this function allows the display of addresses in a format the most clearly reflects the requirements of most postal systems. Needless to say it is likely that most of my systems will at some point transition to the use of this function.
Here is a link to Microsofts documentation on the function CONCAT_WS
Firstly let me review what I am starting to standardise on with regard to address fields which should be a balance between enough detail to store any address on the planet but not so much that it is overly complicated. I’ve expanded their descriptions somewhat. In my experience someone will have created an excel spreadsheet to start the process of recording information, often they standardise on column names such as address01/02/03 etc. If that breakdown exists then I have indicated the fields that I would normally expect to map those fields too.
flatno-buildingdivision houseno-buildno housename-buildname streetfirst-address01 streetsecond locality-address02 towncity-address03 stateoradminlocality postcode-address04 country-address05
And here is an example of the code implemented.
CREATE VIEW v001formattedaddress AS SELECT pkid,
email,
CONCAT_WS(' ',NULLIF(dbo.t001addresstable.firstname,' '), NULLIF(dbo.t001addresstable.surname,' ')) AS fullname,
CONCAT_WS(CHAR(13) + CHAR(10),
NULLIF(dbo.t001addresstable.flatno, ' '),
NULLIF(dbo.t001addresstable.houseno,' '),
NULLIF(dbo.t001addresstable.housename,' '),
NULLIF(dbo.t001addresstable.streetfirst,' '),
NULLIF(dbo.t001addresstable.streetsecond,' '),
NULLIF(dbo.t001addresstable.locality,' '),
NULLIF(dbo.t001addresstable.towncity,' '),
NULLIF(dbo.t001addresstable.stateoradminlocality,' '),
NULLIF(dbo.t001addresstable.postcode,' '),
NULLIF(dbo.t001addresstable.country,' '),
REPLICATE('-', 30) ) AS addressconcat FROM dbo.t001addresstable;
Additionally on reflection for a recent project I made up a list of countries that covers most in the world. For my project I put an include field next to them to allow system administrators to include whether these would be visible in the drop down. Clearly while overtime more and more countries may be added there I would expect it to be years or possibly decades before some of the values of the smaller nations are needed. (For my particular application anyway)
I standardised on the following the 2 digit codes are the ISO Country code standard
AD Andorra AE United Arab Emirates AF Afghanistan AG Antigua and Barbuda AI Anguilla AL Albania AM Armenia AO Angola AQ Antarctica AR Argentina AT Austria AU Australia AW Aruba AX Aland Islands AZ Azerbaijan BA Bosnia and Herzegovina BB Barbados BD Bangladesh BE Belgium BF Burkina Faso BG Bulgaria BH Bahrain BI Burundi BJ Benin BL Saint Barthélemy BM Bermuda BN Brunei Darussalam BO Bolivia, Plurinational State of BQ Bonaire, Sint Eustatius and Saba BR Brazil BS Bahamas BT Bhutan BV Bouvet Island BW Botswana BY Belarus BZ Belize CA Canada CC Cocos (Keeling) Islands CD Congo, the Democratic Republic of the CF Central African Republic CG Congo CH Switzerland CI Cote d’Ivoire CK Cook Islands CL Chile CM Cameroon CN China* CO Colombia CR Costa Rica CU Cuba CV Cape Verde CW Curaçao CX Christmas Island CY Cyprus CZ Czech Republic DE Germany DJ Djibouti DK Denmark DM Dominica DO Dominican Republic DZ Algeria EC Ecuador EE Estonia EG Egypt EH Western Sahara ER Eritrea ES Spain ET Ethiopia FI Finland FJ Fiji FK Falkland Islands (Malvinas) FO Faroe Islands FR France GA Gabon GB United Kingdom GB United Kingdom Northern Ireland GD Grenada GE Georgia GF French Guiana GG Guernsey GH Ghana GI Gibraltar GL Greenland GM Gambia GN Guinea GP Guadeloupe GQ Equatorial Guinea GR Greece GS South Georgia and the South Sandwich Islands GT Guatemala GW Guinea-Bissau GY Guyana HK Hong Kong SAR China HM Heard Island and McDonald Islands HN Honduras HR Croatia HT Haiti HU Hungary ID Indonesia IC Spain Canary Islands IE Ireland Republic IL Israel IM Isle of Man IN India IO British Indian Ocean Territory IQ Iraq IR Iran, Islamic Republic of IS Iceland IT Italy JE Jersey JM Jamaica JO Jordan JP Japan KE Kenya KG Kyrgyzstan KH Cambodia KI Kiribati KM Comoros KN Saint Kitts and Nevis KP Korea, Democratic People’s Republic of KR Korea, Republic of KW Kuwait KY Cayman Islands KZ Kazakhstan LA Lao People’s Democratic Republic LB Lebanon LC Saint Lucia LI Liechtenstein LK Sri Lanka LR Liberia LS Lesotho LT Lithuania LU Luxembourg LV Latvia LY Libyan Arab Jamahiriya MA Morocco MC Monaco MD Moldova, Republic of ME Montenegro MF Saint Martin (French part) MG Madagascar MK Macedonia, the former Yugoslav Republic of ML Mali MM Myanmar MN Mongolia MO Macau SAR China MQ Martinique MR Mauritania MS Montserrat MT Malta MU Mauritius MV Maldives MW Malawi MX Mexico MY Malaysia MZ Mozambique NA Namibia NC New Caledonia NE Niger NF Norfolk Island NG Nigeria NI Nicaragua NL Netherlands NO Norway NP Nepal NR Nauru NU Niue NZ New Zealand OM Oman PA Panama PE Peru PF French Polynesia PG Papua New Guinea PH Philippines PK Pakistan PL Poland PM Saint Pierre and Miquelon PN Pitcairn PS Palestine PT Portugal PY Paraguay QA Qatar RE Reunion RO Romania RS Serbia RU Russian Federation RW Rwanda SA Saudi Arabia SB Solomon Islands SC Seychelles SD Sudan SE Sweden SG Singapore SH Saint Helena, Ascension and Tristan da Cunha SI Slovenia SJ Svalbard and Jan Mayen SK Slovakia SL Sierra Leone SM San Marino SN Senegal SO Somalia SR Suriname SS South Sudan ST Sao Tome and Principe SV El Salvador SX Sint Maarten (Dutch part) SY Syrian Arab Republic SZ Swaziland TC Turks and Caicos Islands TD Chad TF French Southern Territories TG Togo TH Thailand TJ Tajikistan TK Tokelau TL Timor-Leste TM Turkmenistan TN Tunisia TO Tonga TR Turkey TT Trinidad and Tobago TV Tuvalu TW Taiwan TZ Tanzania United Republic of UA Ukraine UG Uganda US United States UY Uruguay UZ Uzbekistan VA Holy See (Vatican City State) VC Saint Vincent and the Grenadines VE Venezuela Bolivarian Republic of VG Virgin Islands, British VN Vietnam VU Vanuatu WF Wallis and Futuna WS Samoa YE Yemen YT Mayotte ZA South Africa ZM Zambia ZW Zimbabwe