1

Why does SQL Server think that CHAR(49) AND CHAR(185) are the same?

When a 1 is entered into a column either of type NVARCHAR or TEXT, it sees it as both CHAR (49) (One) and CHAR (185) (Superscript One)

Example data: 

SELECT CAST('1111' AS TEXT)  AS col1 INTO CharTest;

Select scripts:

SELECT * FROM CharTest WHERE col1 LIKE '%'+CHAR(49)+'%';
SELECT * FROM CharTest WHERE col1 LIKE '%'+CHAR(185)+'%';

Both statements return: 1111

AMouat
  • 685
  • 15
  • 27
  • I tested the scripts . and it works perfectly, the only one script retrieves 1111, and the other doesn't. – ahmed abdelqader Dec 14 '16 at 12:27
  • 1
    is it the collation that you have in force? – Cato Dec 14 '16 at 12:31
  • 1
    Your Example Data is not matched with the Description of issue ! , SELECT 1111 it means the dataType is `int`, so update your question via correct example `NVARCHAR` or `TEXT` – ahmed abdelqader Dec 14 '16 at 12:32
  • @Ahmed - Good spot - you can change the sample to insert '1111' and get the same effects though, varying the COLLATION seems to vary what is returned by LIKE '%1%' – Cato Dec 14 '16 at 12:38
  • @Cato I actually changed the sample to be '1111', but still the issue not created , because it is created only with `NVARCHAR` or `TEXT` and the data type of '1111' is `VARCHAR` – ahmed abdelqader Dec 14 '16 at 12:41
  • My bad edited script – AMouat Dec 14 '16 at 12:44
  • 1
    Not the same scenario, and a different RDBMS, but essentially the same issue: http://stackoverflow.com/questions/41102371/sql-doesnt-differentiate-u-and-%C3%BC-although-collation-is-utf8mb4-unicode-ci/41104208#41104208 – Pred Dec 14 '16 at 12:47
  • 2
    LIKE is by default case-insensitive. And `'1' = char(185) COLLATE Latin1_General_CI_AS` evaluates as true, while a case-sensitive collation `'1' = char(185) COLLATE Latin1_General_CS_AS` evaluates as false. So the exponent number of 1 counts as a different case. Who knew? – LukStorms Dec 14 '16 at 14:04

1 Answers1

6

a different collation should do this

SELECT * FROM CharTest WHERE 
        col1 LIKE '%'+CHAR(49)+'%' COLLATE  SQL_Latin1_General_CP1_CS_AS;
SELECT * FROM CharTest WHERE 
        col1 LIKE '%'+CHAR(185)+'%' COLLATE SQL_Latin1_General_CP1_CS_AS;
Cato
  • 3,652
  • 9
  • 12