My server's default collation is Latin1_General_CI_AS, as determined by this query:
SELECT SERVERPROPERTY('Collation') AS Collation;
I was surprised to discover that with this collation I can match non-digit characters in strings using the predicate
Why in the default collation does this happen? I can't think of a case where this would be useful. I know I can work around the behavior using a binary collation, but it seems like a strange way to implement the default collation.
Filtering digits produces non-digit caracters
I can demonstrate the behavior by creating a column that contains all possible single-byte character values and filtering the values with the digit-matching predicate.
The following statement creates a temporary table with 256 rows, one for each code point in the current code page:
WITH P0(_) AS (SELECT 0 UNION ALL SELECT 0), P1(_) AS (SELECT 0 FROM P0 AS L CROSS JOIN P0 AS R), P2(_) AS (SELECT 0 FROM P1 AS L CROSS JOIN P1 AS R), P3(_) AS (SELECT 0 FROM P2 AS L CROSS JOIN P2 AS R), Tally(Number) AS ( SELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM P3 ) SELECT Number AS CodePoint, CHAR(Number) AS Symbol INTO #CodePage FROM Tally WHERE Number >= 0 AND Number <= 255;
Each row contains the integer value of the code point, and the character value of the code point. Not all of the character values are displayable - some of the code points are strictly control characters. Here is a selective sample of the output of
SELECT CodePoint, Symbol FROM #CodePage:
0 1 2 ... 32 33 ! 34 " 35 # ... 48 0 49 1 50 2 ... 65 A 66 B 67 C ... 253 ý 254 þ 255 ÿ
I would expect to be able to filter on the Symbol column to find digit characters using a LIKE predicate and specifying the range of characters '0' thru '9':
SELECT CodePoint, Symbol FROM #CodePage WHERE Symbol LIKE '[0-9]';
It produces a surprising output:
CodePoint Symbol 48 0 49 1 50 2 51 3 52 4 53 5 54 6 55 7 56 8 57 9 178 ² 179 ³ 185 ¹ 188 ¼ 189 ½ 190 ¾
The set of code points 48 thru 57 are the ones I expect. What surprises me is that the symbols for superscripts and fractions are also included in the result set!
There might be a mathematical reason to think of exponents and fractions as numbers, but it seems wrong to call them digits.
Using binary collation as a workaround
I understand that to get the result I expect, I can force the corresponding binary collation Latin1_General_BIN:
SELECT CodePoint, Symbol FROM #CodePage WHERE Symbol LIKE '[0-9]' COLLATE Latin1_General_BIN;
The result set includes only the code points 48 thru 57:
CodePoint Symbol 48 0 49 1 50 2 51 3 52 4 53 5 54 6 55 7 56 8 57 9