The title is a bit generic, but the specific issue is that the United Kingdom of Great Britain and Northern Ireland has two commonly used 2-character codes. ISO 3166-1 specifies the code
GB as the proper code, but many people still use
UK for that country.
Creating a separate table to store alternate country codes and updating all my queries against the main table to also perform a secondary lookup on the alternate codes table seems like overkill for a single country. At this time, I only know of that one country where this may be a problem - at least with Alpha-2 country codes.
So what I'm wondering, is if there is a way to construct a table or column such that querying a single
nchar(2) column with either
GB will return the same record?
I know how to write individual queries to do this, but that means updating many queries in many places (stored procedures, views, UDFs, and even application code outside the database). I'm almost wondering if I can code a custom collation, inheriting from an existing collation and just add something to make the two character sequences equal to each other..?