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.