Search for Arabic text ignoring diacritics, alef hamza differences, and kashida in SQL Server and Oracle Well, I have a table with an Arabic column which may contain variant forms of the same word in multiple rows, for example the word "أسمى" might be in the following forms:


with diacritics: "أَسْمَى";
with changing the last letter of "ى" into "ي" so it would be like "أسمي";
with kashida or "ـ" in some part of the word so it would possibly be "أسمــى";
with variant forms of alef hamza (أ - إ - ا - ء), so it might be "اسمى" or "إسمى";
any combination of the former cases, i.e diacritics and kashida.


I'm looking for a way to store these values in the database (actually I need a solution for SQL Server and another one for Oracle), and to retrieve them regardless of these differences.

I found that I should use an Arabic collation like this Arabic_CI_AI but this collation only helped me in sorting out the problems #1 and #2.

In addition I considered using a fulltext index on the column, but this has its drawbacks and it doesn't provide a full solution.

How can I resolve this?
Not sure but based on my understanding, this is related to the language/character set.

I use Oracle database and I do store arabic values within the database.

The character set I use is UTF8, I have also tried storing the different examples provided for the same word and I see it working fine.

I am sure SQL Server also supports such character set.