1

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 UK or 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..?

2

This isn't a perfect solution but may work for you.

Rename your source table.

EXEC sp_rename 'countries','countries_source'

Then put a view on top of it

CREATE VIEW countries AS
SELECT fields, 
    CASE WHEN CountryCode = 'GB' THEN 'UK' ELSE CountryCode END AS CountryCode
FROM countries_source

Your existing SELECT queries won't have any problems. I think you will have to modify your UPDATE queries and might have to update your INSERT and DELETE queries as well. But some quick tests should let you know if that's the case.

  • Neat idea, but this will just change the SELECTs return value for the column from "GB" to "UK". This will help when the comparison data is "UK" but cause a problem for "GB". I'm really wishing I could create and install a custom collation, or that SQL Server had SELECT triggers. – Zarepheth Mar 14 '16 at 15:21
  • This gives me an idea that may work; though I'll need to wait an hour so to try it. Use a UNION in the VIEW definition. It could return the one record as two records, each having a unique value in the [CountryCode] field. That would enable SELECT statements to get the same surrogate primary key and link to the correct records in other tables. – Zarepheth Mar 14 '16 at 15:28
  • Yea, this was just an example of what you could do. I wasn't sure which direction you needed to go (split so you have both codes, or you have both codes and want to merge them to one). – Kenneth Fisher Mar 14 '16 at 15:34
  • Well, due to a long running test, I can't update the table, but using a different name I did test a view using UNION. This works for with my expected WHERE clause. So long as I don't need the real ISO 3166-1 Alpha-2 country code after the select, I'll be okay. – Zarepheth Mar 14 '16 at 15:37
1

If you don't want to alter your statements, can you alter the data?

Lacking a SELECT trigger, I'd update all entries in the column to be 'GB' then add a trigger to the table to make sure all values inserted or updated are changed from UK to GB.

You're other option is re-writing your queries, which seems like a lot of work. Having something that will automagically change the values isn't very transparent and might prove a nightmare for anyone down the line trying to troubleshoot it. Best bet: fix the data and don't hack it :)

  • I was thinking of triggers for my [Countries] table, but as you point out, there are no SELECT triggers. I'm using the [Countries] table as a lookup to store the full country name in another table. If the application cannot find the country name, it substitutes the raw data - which is where I'm seeing UK. Perhaps an INSERT or UPDATE trigger on the destination table to change UK to the full name would work... – Zarepheth Mar 14 '16 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.