Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 LIKE '[0-9]'.

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
share|improve this question
    
"There might be a mathematical reason to think of exponents and fractions as numbers" Only think of them as numbers? Why, aren't they numbers? –  ypercube Feb 14 '13 at 19:32
    
@ypercube Fractions: Yes, they are. Exponents: No, insofar as it makes no sense to me to write an exponent in isolation. What does ² by itself mean? Is ² equal to 2? Is it merely a question of notation? As far as collation is concerned, I think it would be equally valid to, say, sort all the digits in sequence, then sort all the exponents in sequence, and so on. –  Iain Elder Feb 14 '13 at 19:42

2 Answers 2

up vote 17 down vote accepted

[0-9] is not some type of regular expression defined to just match digits.

Any range in a LIKE pattern matches characters between the start and end character according to collation sort order.

SELECT CodePoint,
       Symbol,
       RANK() OVER (ORDER BY Symbol COLLATE Latin1_General_CI_AS) AS Rnk
FROM   #CodePage
WHERE  Symbol LIKE '[0-9]' COLLATE Latin1_General_CI_AS
ORDER  BY Symbol COLLATE Latin1_General_CI_AS 

Returns

CodePoint            Symbol Rnk
-------------------- ------ --------------------
48                   0      1
188                  ¼      2
189                  ½      3
190                  ¾      4
185                  ¹      5
49                   1      5
50                   2      7
178                  ²      7
179                  ³      9
51                   3      9
52                   4      11
53                   5      12
54                   6      13
55                   7      14
56                   8      15
57                   9      16

So you get these results because under your default collation these characters sort after 0 but before 9.

It looks as though the collation is defined to actually sort them in mathematical order with the fractions in the correct order between 0 and 1.

You could also use a set rather than a range. To avoid 2 matching ² you would need a CS collation

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0123456789]' COLLATE Latin1_General_CS_AS
share|improve this answer
    
@RemusRusanu - Yes I tested. For the range query it still shows up as it still gets sorted next to 2 but for the equality comparison in the set it depends on case sensitivity. –  Martin Smith Feb 14 '13 at 12:15
1  
You're right, my test was flawed, CS considers the super/sub script. sqlfiddle.com/#!6/d41d8/2540 –  Remus Rusanu Feb 14 '13 at 12:20
    
I hadn't thought about using the ORDER BY clause to examine the how the collation actually orders all the characters, but your example makes it clear. The predicate LIKE '[0-9]' means 'match 0, 9, and all characters that the collation sorts between these two values'. Try the predicate LIKE '[A-B]' to see how many accented A-like characters sort between A and B! –  Iain Elder Feb 14 '13 at 13:40
2  
Weird, I wonder why ¹ comes before 1 but ² comes after 2 –  BlueRaja - Danny Pflughoeft Feb 14 '13 at 17:25
2  
@BlueRaja-DannyPflughoeft I think it's because in a case-insensitive collation they are considered equal, so either order is correct. The database engine chooses one order or the other on a per-comparison basis. When I inspect the result of SELECT CodePoint, Symbol FROM #CodePage ORDER BY Symbol;, I see that A sorts before a, but b sorts before B. –  Iain Elder Feb 14 '13 at 19:48

Latin1 is code page 1252, in which 178 is 'SUPERSCRIPT TWO'. This is an Unicode superscript: is the character "2" as superscript. According to the Unicode Technical Standard #10 it should compare equal to 2, see 8.1 Collation Folding:

Map compatibility (tertiary) equivalents, such as full-width and superscript characters, to representative character(s)

The bug would be if superscript 2 would compare different from 2! Before you say 'but my column is not Unicode', rest assured: according to MSDN (see Windows Collations) all string comparison and sorting are done according to the Unicode rules, even when the on-disk representation is CHAR.

As for the other characters in your example, like VULGAR FRACTION ONE QUARTER and the like they do not compare equal to any number, but, as Mark already showed, they do sort properly between 0 and 9.

And, of course, if you'd change the code page you would get different results. Eg. with Greek_CS_AS (code page 1253) you would get the characters with code 178, 179 and 189.

share|improve this answer
    
Thanks for reference to the Unicode algorithm. It's complex, but thorough. I added a reference to MSDN that backs up your claim about non-Unicode string comparison. –  Iain Elder Feb 14 '13 at 16:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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