SQL Server not difference between 'ی' and 'ي' in Arabic_CI_AS collation

Question

I'm using ASCII function for getting equivalent ASCII code of two characters, but I'm surprised when seeing there is no difference between 'ي' and 'ی', can anyone help me?

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

Answer 1

Because your character is non Unicode you have to use UNICODE() function instead of ASCII() .

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

will result: 237, 237

but

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

will result: 1610, 1740

Answer 2

Try this

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

Answer 3

Another solution by using the proper collate in case you want to use Ascii

Arabic_CS_AS_KS

result will come as ى = 236 and ي= 237

Answer 4

This is a limitation ASCII 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 are made up 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 collation when they are properly marked as multi-byte:

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 when you mark the 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 As TT. points out, these characters don't have an entry in the ASCII code table.