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:

  1. with diacritics: "أَسْمَى";

  2. with changing the last letter of "ى" into "ي" so it would be like "أسمي";

  3. with kashida or "ـ" in some part of the word so it would possibly be "أسمــى";

  4. with variant forms of alef hamza (أ - إ - ا - ء), so it might be "اسمى" or "إسمى";

  5. 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?

  • 2
    In a dev environment like .NET remove the diacritics and store the value in a search column. stackoverflow.com/questions/249087/…
    – paparazzo
    Apr 14, 2016 at 12:37
  • thanks Paparazzi, but I'm aware of this. this has the drawback of loosing the exact words, while my situation requires that I should maintain the original text as is, in addition to the search capability. and using a search column would double the required storage Apr 14, 2016 at 12:42
  • 1
    @Creative_Q - Paparazzi means add an additional column to store the simplified form of each word, and use that for the search function only.
    – Hannah Vernon
    Apr 14, 2016 at 13:43
  • 2
    well, yes, of course it would. @Paparazzi offered that as a solution to your problem, he didn't say it wouldn't take extra storage.
    – Hannah Vernon
    Apr 14, 2016 at 14:10
  • 2
    You could consider changing collations. In the past (for Greek) I've stored the exact data under Latin1_General_100_CS_AS and then had another "searchable" column with Latin1_General_100_CI_AI which ignores case and accents. Arabic_CS_AI and Arabic_CI_AI exist
    – swasheck
    Oct 12, 2018 at 16:21

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.