4

Running this results in "test", as expected:

SELECT
    REPLACE(NCHAR(1234), NCHAR(1234), N'test');

However, running this results in "aӒa", which doesn't have "test" in it:

SELECT
    REPLACE(N'a' + NCHAR(1234) + N'a', NCHAR(1234), N'test');

I thought it might have to do with the string concatenation happening for the haystack but not the needle, but when I tried this, it still didn't "work":

SELECT
    REPLACE(N'a' + NCHAR(1234) + N'a', N'' + NCHAR(1234) + N'', N'test');

Result: "aӒa"

I suspected it might have to do with how it's interpreting the characters, so I tried specifying a binary collation... and that "fixed" the issue:

SELECT
    REPLACE(N'a' + NCHAR(1234) + N'a' COLLATE Latin1_General_100_BIN2, NCHAR(1234), N'test');

Result: "atesta".

Why?

This behavior seems to exist for some characters, but not others.

SELECT
    REPLACE(N'a' + NCHAR(23423) + N'a', NCHAR(23423), N'test');

Result: "atesta" ("works")

SELECT
    REPLACE(N'a' + NCHAR(5342) + N'a', NCHAR(5342), N'test');

Result: "aᓞa" (doesn't "work")

Why?

Improve this question

1 Answer 1

Reset to default
6

This behavior is simply due to the fact that you are executing the query in a Database that has a pre-"100" series default Collation, in which case a great many characters do not have any sort weight. No sort weight means that they equate to empty string. They have a value of 0. So they always equal each other when nothing else having a sort weight is involved. They also equate to empty string. The version 100 Collations (starting in SQL Server 2008 which is version 10.0 or 100 without the decimal / minor version) added sort weights for most characters. So, just force the collation by adding COLLATE Latin1_General_100_CI_AS_SC. A binary Collation of any version (one ending in _BIN2, don't use Collations ending in just _BIN) also works as there are no such things as sort weights in binary Collations.

SELECT 1 WHERE NCHAR(1234) = '' COLLATE SQL_Latin1_General_CP1_CI_AS;
-- 1

SELECT 2 WHERE NCHAR(1234) = '' COLLATE Latin1_General_CI_AS;
-- 2

SELECT 3 WHERE NCHAR(1234) = '' COLLATE Latin1_General_100_CI_AS;
-- (no results)

SELECT 4 WHERE NCHAR(1234) = '' COLLATE Latin1_General_BIN2;
-- (no results)

Even multiple characters with no weight still equate to empty string (or any number of characters with no sort weight):

SELECT 5 WHERE NCHAR(1234) + NCHAR(1234) + NCHAR(1234)
       = N'' COLLATE Latin1_General_CS_AS_KS_WS;
-- 5

In this next test, NCHAR(1234) equates to itself only when using a version 100 (or newer) Collation. Pre-version 100 Collations assign no sort weight to NCHAR(1234) such that it doesn't equate to anything, hence it was previously not found in the first expression.

SELECT REPLACE(N'a' + NCHAR(1234) + N'a',
               NCHAR(1234) COLLATE Latin1_General_100_CI_AS,
               N'test');
-- atesta

Continuing example # 5 above, we can prove that 3 characters having no weight equate to 2 different characters that also have no weight:

SELECT 6 WHERE NCHAR(1234) + NCHAR(1234) + NCHAR(1234)
             = NCHAR(5342) + NCHAR(5342) COLLATE Latin1_General_CS_AS_KS_WS;
-- 6

But switching to a version 100 Collation changes that:

SELECT 7 WHERE NCHAR(1234) + NCHAR(1234) + NCHAR(1234)
             = NCHAR(5342) + NCHAR(5342) COLLATE Latin1_General_100_CS_AS_KS_WS;
-- (no results)

SELECT 8 WHERE NCHAR(1234) = NCHAR(5342) COLLATE Latin1_General_100_CS_AS_KS_WS;
-- (no results)

Please see the "Supplementary Characters" characters section of the following post of mine. In that section I show similar behavior as it relates to supplementary characters, and at the end of the section I show how many BMP characters (i.e. non-supplementary characters) match one of these "missing weights" characters per Collation version.

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Identifiers, Part 3 of 2 (Delimited Identifiers)

For more info on working with Collations, please visit: Collations.info

Improve this answer
8
  • Wow. I would never have expected NCHAR(1234) to equal empty string in any collation. There are so many checks against empty string in code which would fail. (I guess LEN(@a) > 0 is much more reliable.) Why would they have that default behavior? Why not pick some other arbitrary value for unknown characters to equal than empty string/? Just... wow.
    – Riley Major
    Commented May 31, 2018 at 19:16
  • It is not an arbitrary thing. each character has a multi-valued "weight". when sorting, those weights are compared to each other per each category (there are generally 4 categories / levels). but if they have no weights assigned, then they are effectively 0, which is just what an empty string also evaluates to. And so they didn't choose an empty string to equate to, it is just a function of them "forgetting" to add weights for some 30,000 of the 62,000 (or so) defined characters. I was going to add a link to my blog post where I tested all characters and found the exact count.
    – Solomon Rutzky
    Commented May 31, 2018 at 19:19
  • I get that unassigned weights are effectively 0, and I get that the characters probably didn't exist when the collation was created, but (1) why not use 1 instead of 0 for missing weights and (2) why are characters considered equal just because their weights are both 0? That seems like "bad" behavior.
    – Riley Major
    Commented May 31, 2018 at 19:25
  • 1
    These characters did exist. They were just not given weights for some reason. But they can't be assigned "1" since there are plenty of characters that validly have no weights, such that they don't influence sorting (e.g. a lot of punctuation). And, they are considered equal because we are talking about linguistic rules here, same rules for sorting and comparison. A more "accurate" system would separate sorting and comparison operations and allow for different rules for each (i.e. case insensitive comparison, but case sensitive sorting). SQL Server puts them together, as do most systems.
    – Solomon Rutzky
    Commented May 31, 2018 at 19:34
  • 1
    Yes, it skipped them because it cannot see them. They do not equal anything. Whether individual or multiples of them, they are 0. They don't even take up a position. Unicode sorting allows for certain characters to be "ignorable". Try this: SELECT tmp.* FROM (VALUES ('aa'), ('ac'), ('a-b')) tmp(col) ORDER BY tmp.col; SELECT tmp.* FROM (VALUES (N'aa'), (N'ac'), (N'a-b')) tmp(col) ORDER BY tmp.col; In Unicode, the dash, at least when combined with letters, is ignored entirely.
    – Solomon Rutzky
    Commented May 31, 2018 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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