Export full state name - not abbreviation

Question

In the CiviCRM database the States have a full name and an abbreviation. e.g. New York, NY; Paris, 75; British Columbia, BC.

The contact export on CiviCRM exports the abbreviation for the state field not the full state name. This is fine for US and Canada but doesn't work fore UK and Ireland.

I've used CiviToken which has a token that adds the full name. But this is only good for labels and templates. I need an export to do an offline mail merge for a printer.

I've found this from searching but nothing since https://forum.civicrm.org/index.php%3Ftopic=21897.0.html https://forum.civicrm.org/index.php%3Ftopic=28789.0.html

Is there a way to do this?

Could it be added as a core feature. It's a function that anyone in UK and Ireland would need.

I'm on CiviCRM 5.14.1

All the best James

25 Nov 2021: Update on this issue: In Nov 2020 I did the changes suggested above in https://civicrm.stackexchange.com/a/37952/8411

• copied civicrm_state_province name to civicrm_state_province abbreviation for all countries apart from US and CA.
• changed field civicrm_state_province abbreviation and log_civicrm_state_province abbreviation to varchar(64)

A few months ago the State / Province field stopped loading the saved values from the database on the webforms where users edit address. They still saved the values.

Now after upgrading to CiviCRM 5.43.2 I started getting an error “State cannot be longer than 5 characters but is currently 10 characters long.” when saving the webform. This isn’t coming from the database as I changed the field civicrm_state_province abbreviation to be varchar(64) as noted above. New validations of the state field for 4 characters must have been introduced in the code.

I implimented this solution. I reverted the changes above to the abbreviation field. Instead I edited code to get the export function to return civicrm_state_province name instead of abbreviation. Details follow:

/httpdocs/sites/all/modules/civicrm/CRM/Core
PseudoConstant.php
function stateProvinceAbbreviation
$query = "SELECT abbreviation
replaced
$query = "SELECT name

It would be great if this could be done in the core. Or if the core export function could export the civicrm_state_province name and abbreviation instead of just abbreviation.

This must be an issue for a lot of users outside USA and Canada where these abbreviations are not commonly used.

Answer 1

The more general issue about state has come up before, e.g. https://lab.civicrm.org/dev/core/-/issues/724 and https://lab.civicrm.org/dev/core/-/issues/1709.

It's not a full solution but you can manually update the abbreviations in the database in the civicrm_state_province table, but you'd have to change the column size in the table definition, and someday an upgrade might wipe out your changes, although unlikely.