SELECT query: filter unable to distinguish between two different characters

Question

In a recent support case, an error was traced to the difference between two similar-looking characters: ― (Unicode U+2015) and ー (Unicode U+30FC). A select statement with a WHERE clause was unable to distinguish between the two characters. Below is a simplified reproduction:

USE master;
GO

-- Create test DB.
CREATE DATABASE Test COLLATE Japanese_CI_AS;
GO

USE Test;
GO

-- Create test table.
CREATE TABLE Foo
(
    id INT NOT NULL PRIMARY KEY,
    descr NVARCHAR(50) NOT NULL
);

-- Insert test data.
INSERT INTO Foo (id, descr)
    VALUES (1, NCHAR(0x2015))
        , (2, NCHAR(0x30fc));

-- Select data.
-- Regardless of the WHERE, cannot distinguish between the two different characters.
SELECT * FROM Foo WHERE descr = NCHAR(0x2015);
SELECT * FROM Foo WHERE descr = NCHAR(0x30fc);

-- Only able to distinguish characters by adding a binary collation.
SELECT * FROM Foo WHERE descr = NCHAR(0x2015) COLLATE Japanese_Unicode_BIN
SELECT * FROM Foo WHERE descr = NCHAR(0x30fc) COLLATE Japanese_Unicode_BIN2;

The resolution was to fix the data to use only one of the characters. However, I am at a loss to explain why the SELECT query is unable to distinguish between the two characters without manually applying a binary collation. I'd appreciate if someone could explain what is happening here so that similar issues can be avoided in the future.

Answer 1

Both of those characters actually match more than just each other. Testing in both SQL Server 2012 SP3 and SQL Server 2016 SP1, and across the following 4 Collations (everything-sensitive) in each of those versions:

• Japanese_XJIS_100_CS_AS_KS_WS_SC (16)
• Japanese_90_CS_AS_KS_WS_SC (12)
• Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_SC (15)
• Japanese_Unicode_CS_AS_KS_WS (13)

I found that each of those 2 characters matched anywhere from 10 - 14 additional characters (numbers in parenthesis in list above is total matches for the group that includes the two characters in the question):

Dec     Hex         Character
8213    0x2015      ―
12293   0x3005      々
12337   0x3031      〱
12338   0x3032      〲
12339   0x3033      〳
12340   0x3034      〴
12341   0x3035      〵
12347   0x303B      〻
12445   0x309D      ゝ
12446   0x309E      ゞ
12448   0x30A0      ゠
12540   0x30FC      ー
12541   0x30FD      ヽ
12542   0x30FE      ヾ
40981   0xA015      ꀕ
65392   0xFF70      ｰ

You can test with something along the lines of:

SELECT NCHAR(0x2015), NCHAR(0x30fc)
WHERE NCHAR(0x2015) = NCHAR(0x30fc) COLLATE Japanese_Unicode_CS_AS_KS_WS;

However, testing via the ICU Collation Demo (be sure to uncheck "diff strength" and check "sort key"), selecting either one of the two Japanese options from the drop-down, and using any combinations of radio-buttons, doesn't ever produce a match for U+2015, though U+30FC does seem to match U+FF70. So, based on this, it would appear that perhaps these two characters shouldn't match. It is always possible that one or more weights was entered incorrectly.

However (again), we must also keep in mind that what we see in that ICU Collation demo and on the Unicode site (the main pages, at least -- not prior versions) isn't the same version of Unicode that SQL Server is using. SQL Server is using either version 4 or 5 of Unicode, while the current stuff we see in those two places online is version 10..

Hence, unfortunately, you might have to stick with occasionally using a binary collation to distinguish between these characters.

---

Other notes:

• Don't use the deprecated _BIN Collations. Just use the newer / current _BIN2 Collations
• Just FYI, for binary Collations, the locale doesn't make a difference for NVARCHAR data. And the only difference for VARCHAR is the Code Page that is used (i.e. the character set). Meaning, with NVARCHAR data, there is no difference between Japanese_Unicode_BIN2 and Latin1_General_100_BIN2.

• The new "Variation Selector Sensitive" (_VSS) Collations introduced in SQL Server 2017 do not help with this. This is primarily due to Variation Selectors being specific code points in the range of U+FE00 to U+FE0F and supplementary code points U+E0100 to U+E01EF. The characters in question here are not in either of those ranges. Also, Variation Selectors are zero-width, non-printable combining characters that attach to the preceding character.

  There was a slight chance that the newer Collations would handle these characters better simple by being newer and hence having the opportunity to have weights added or fixed. However, there was no improvement as discovered using the following test:

  ;WITH cte AS
  (
    SELECT TOP (65535) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [Num]
    FROM [master].[sys].[columns] col1
    CROSS JOIN [master].[sys].[columns] col2
  )
  SELECT cte.[Num], NCHAR(cte.[Num])
  FROM cte
  WHERE NCHAR(cte.[Num]) COLLATE Japanese_XJIS_140_CS_AS_KS_WS
       IN (NCHAR(0x2015), NCHAR(0x30fc))
  

  Both Japanese_Bushu_Kakusu_140_CS_AS_KS_WS_VSS and Japanese_XJIS_140_CS_AS_KS_WS_VSS returned the same rows as Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_SC and Japanese_XJIS_100_CS_AS_KS_WS_SC, respectively.