CODEWITHSUNDEEP
sqlsql-serverunicode
Vasyl Senko
Vasyl Senko

Reputation: 1829

sql like matches to different characters

There is Unicode character [¹] - Superscript One (in Latin-1 Supplement block):

SELECT NCHAR(0x00b9) --returns ¹

But trying to find out is that symbols exist in column next query returns rows which contain digit one (0x0031):

SELECT [Name] FROM [Company] WHERE [Name] like '%'+nchar(0x00b9)+'%'

Update: I am using MSSQL

Upvotes: 0

Views: 150

Answers (1)

Ebis
Ebis

Reputation: 380

It's because your database has a Case INsensitive Collation:

SELECT DATABASEPROPERTYEX (DB_NAME(DB_ID()), 'COLLATION') -- = Latin1_General_CI_AS

SELECT [Name] AS [WHERE_Collation caseINsensitive] 
FROM   ( VALUES  ('¹')
              ,  ('1') ) AS Company (Name) 
WHERE [Name] LIKE '%'+nchar(0x00b9)+'%' 
;

Result:

WHERE_Collation caseINsensitive

¹

1

SELECT [Name] AS [Collation caseSensitive] 
FROM   ( VALUES  ('¹')
              ,  ('1') ) AS Company (Name) 
WHERE [Name] LIKE '%'+nchar(0x00b9)+'%' COLLATE Latin1_General_CS_AS
;

Result

WHERE_Collation caseSensitive

¹

Upvotes: 1

Related Questions