In Arabic we have characters like ا (alef) and أ (alef with hamza).
Users write them interchangeably and we want to search them interchangeably. SQL Server treats them as separate characters. How can I make SQL treat them as the same character?
I thought to replace any أ (alef with hamza) with ا (alef) at insertion but we have a lot of alternatives in Arabic language not just ا (alef) and أ (alef with hamza).
Arabic_CI_AI but that doesn't solve the problem.
Here is a script to regenerate the issue:
CREATE TABLE [dbo].[TestTable] ( [ArabicChars] [nvarchar](50) NOT NULL, CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ( [ArabicChars] ASC ) ) ON [PRIMARY]; INSERT INTO TestTable values (N'احمد'); INSERT INTO TestTable values (N'أحمد'); SELECT * FROM TestTable WHERE ArabicChars like N'ا%';
The result is:
ArabicChars احمد (1 row(s) affected)
The desired result would be both of the rows we inserted.