Sql-server – Can a table column be written to treat two different values as the same value

collationsql serversql-server-2008-r2

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

Best Answer

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.

Related Solutions

SQL Server – Why Non-Digits Match LIKE [0-9]

[0-9] is not some type of regular expression defined to just match digits.

Any range in a LIKE pattern matches characters between the start and end character according to collation sort order.

SELECT CodePoint,
       Symbol,
       RANK() OVER (ORDER BY Symbol COLLATE Latin1_General_CI_AS) AS Rnk
FROM   #CodePage
WHERE  Symbol LIKE '[0-9]' COLLATE Latin1_General_CI_AS
ORDER  BY Symbol COLLATE Latin1_General_CI_AS

Returns

CodePoint            Symbol Rnk
-------------------- ------ --------------------
48                   0      1
188                  ¼      2
189                  ½      3
190                  ¾      4
185                  ¹      5
49                   1      5
50                   2      7
178                  ²      7
179                  ³      9
51                   3      9
52                   4      11
53                   5      12
54                   6      13
55                   7      14
56                   8      15
57                   9      16

So you get these results because under your default collation these characters sort after 0 but before 9.

It looks as though the collation is defined to actually sort them in mathematical order with the fractions in the correct order between 0 and 1.

You could also use a set rather than a range. To avoid 2 matching ² you would need a CS collation

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0123456789]' COLLATE Latin1_General_CS_AS

MySQL Collation – Handling Different Mathematical Symbols

From 10.1.14.1 Unicode Character Sets in the MySQL 5.5 Reference Manual (emphasis added):

For supplementary characters in general collations, the weight is the weight for 0xfffd REPLACEMENT CHARACTER. For supplementary characters in UCA collations, their collating weight is 0xfffd. That is, to MySQL, all supplementary characters are equal to each other, and greater than almost all BMP characters.

and:

The current rule that all supplementary characters are equal to each other is nonoptimal but is not expected to cause trouble. These characters are very rare, so it will be very rare that a multi-character string consists entirely of supplementary characters.

So the answer to your question appears to be "no".

finally:

If you really want rows sorted by MySQL's rule and secondarily by code point value, it is easy:

ORDER BY s1 COLLATE utf32_unicode_ci, s1 COLLATE utf32_bin

Though it is not clear to me how this should be applied for comparisons.

The documentation extracts quoted above are unchanged for MySQL 5.7.

Best Answer

Related Solutions

SQL Server – Why Non-Digits Match LIKE [0-9]

MySQL Collation – Handling Different Mathematical Symbols

Related Question