Played with it a bit and found this returns the second answer and not exactly sure why. I am thinking there is an implicit conversion changing the collation but not sure how it got what it did.
WHERE testString LIKE N'[a-B]';
The underlying reason for the behavior in either case is that when sorting using a case-sensitive collation, there will be consistent ordering of each case of each letter, but there are two cases, hence two options for the ordering. In terms of the given dataset for this question, that would be reflected as:
- Upper-case first = A, a, B, b, C, c
- Lower-case first = a, A, b, B, c, C
Most of the SQL Server collations (names starting with "
SQL_") use "upper-case first" for
VARCHAR data only, but use "lower-case first" for
NVARCHAR data. Windows collations (names not starting with "
SQL_") uses "lower-case first" for both
NVARCHAR data. Binary collations are not relevant here because they have no concept of case.
Now, the range option of the
[...] wildcard uses sorting to determine the actual range. Looking at the two lists shown above and applying them to the pattern of
[a-B] results in:
- Upper-case first = _, a, B, _, _, _
- Lower-case first = a, A, b, B, _, _
The reason why you got the "lower-case first" result from adding the upper-case "N" prefix to the string literal (i.e.
N'[a-B]') is that doing so creates a Unicode /
NVARCHAR string literal, and datatype precedence causes the
VARCHAR data in the column to be implicitly converted into
NVARCHAR which uses "lower-case first" ordering.
For more info on collations / Unicode, please visit: Collations Info