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?