SQL Server is no different between ی and ي in Arabic_CI_AS mapping

Question

I use the ASCII function to get the equivalent two-character ASCII code, but I am surprised to see that there is no difference between 'ي' and 'ی', can someone help me?

SELECT ASCII('ي'), ASCII('ی') 

Answer 1

Since your character is not Unicode, you should use the UNICODE() function instead of ASCII() .

 SELECT ASCII('ي'), ASCII('ی') 

will result in: 237 , 237

but

 SELECT UNICODE(N'ي'), UNICODE(N'ی') 

will result in: 1610 , 1740

Answer 2

try it

 SELECT UNICODE(N'ي'), UNICODE(N'ی') 

Answer 3

Another solution using the right selection if you want to use Ascii

 Arabic_CS_AS_KS 

the result will be ى = 236 and ي = 237

Answer 4

This is an ASCII restriction function. According to the documentation , ASCII :

Returns the ASCII code value of the leftmost character of a character expression.

However, the characters in your question consist of more than one byte. It appears that ASCII can only read one byte.

When you use these characters as string literals without the N prefix, they are treated as single-byte characters. The following query shows that SQL Server does not treat these characters as equal in the Arabic_CI_AS mapping if they are correctly marked as multibyte:

 SELECT CASE WHEN 'ي' COLLATE Arabic_CI_AS <> 'ی' COLLATE Arabic_CI_AS THEN 1 ELSE 0 END AS are_different_ascii, CASE WHEN N'ي' COLLATE Arabic_CI_AS <> N'ی' COLLATE Arabic_CI_AS THEN 1 ELSE 0 END AS are_different_unicode 

The following query shows the bytes that make up the characters:

 SELECT CAST(N'ي' COLLATE Arabic_CI_AS as varbinary(4)), CAST(N'ی' COLLATE Arabic_CI_AS as varbinary(4)), CAST('ي' COLLATE Arabic_CI_AS as varbinary(4)), CAST('ی' COLLATE Arabic_CI_AS as varbinary(4)) 

However, even if you mark characters as unicode, the ASCII function returns the same value because it can only read one byte:

 SELECT ASCII(N'ي' COLLATE Arabic_CI_AS) , ASCII(N'ی' COLLATE Arabic_CI_AS) 

---

EDIT Like TT. indicates that these characters do not have an entry in the ASCII code table.