0

I am storing Arabic names in my database. In Arabic, there are some letters that may be written in different forms such as "ا"، "أ"، "آ" it all represent the same letter. Also, "ه" ، "ة".

I need to search the database for names, ignoring the differences between "ا"، "أ"، "آ" and also the differences between "ه" ، "ة".

So, for example when user enters in the search box "اسامة", it should return "أسامة"، "اسامة"، "أسامه، "اسامه ... etc. Another example, "فايز"، "فائز" should return both.

How could I do that using SQL Server? How can I search for similar names, not the same name?

I tried using the LIKE keyword, but it's not working:

select * from employee WHERE fname like "%أسامة%" and mname="علي" and lname="الجاسم"
Thom A
  • 88,727
  • 11
  • 45
  • 75
Bassem Ahmed
  • 17
  • 2
  • 2
    Read the comments under [this question](https://dba.stackexchange.com/q/135278/108176). – 41686d6564 stands w. Palestine Dec 19 '21 at 23:15
  • 2
    `like "%أسامة%"` means where the value is like the contents of the **column** called `%أسامة%`. I hope you don't really have a column with that name. – Thom A Dec 19 '21 at 23:18
  • @Larnu I think the [c#] tag is relevant here because answeres might suggest solutions that tackle the problem outside of the database environment. See [this comment](https://dba.stackexchange.com/questions/135278/search-for-arabic-text-ignoring-diacritics-alef-hamza-differences-and-kashida#comment254244_135278), for example. – 41686d6564 stands w. Palestine Dec 19 '21 at 23:23
  • @Larnu i can live with any solution using sql server or ef or even extension method can do trick using c# so – Bassem Ahmed Dec 19 '21 at 23:32
  • 1
    @41686d6564 there is *nothing* in this question about C# . If the OP is looking for a C# solution, perhaps with a CLR function, they should state so and demonstrate their attempt thus far. The fact that a question might be able to be answered with C# doesn't make it about C#. If that is the case, the OP should make the [edit] to add the tag back in along with the information to make the question about C#. – Thom A Dec 19 '21 at 23:39
  • @larnu k i was thinking of something like use c# advantage form normlize database but if its will be much better if we can solve it with db – Bassem Ahmed Dec 19 '21 at 23:47
  • 1
    In the english langauge this is probably just an "accent insensitive" collation. Not sure in the arabic language - perhaps have a look throught the list of collations. – Nick.Mc Dec 20 '21 at 04:40
  • There's a whole bunch of collations listed here - which one are you using ? I imagine you are already using sometihng like `Arabic_CI_AI`? (case insensitive, accent insensitive) https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15 – Nick.Mc Dec 20 '21 at 04:45
  • 1
    Aside... in Microsoft SQL server the double quotes `"` are often used to delimit column and object names, single quotes `'` delimit string literals and the N-prefix `N'...'` to delimit National character string literals containing (non-ASCII) Unicode characters. Instead of `fname like "%أسامة%" and mname="علي" and lname="الجاسم"` you probably need `fname like N'%أسامة%' and mname=N'علي' and lname=N'الجاسم'` instead. – AlwaysLearning Dec 20 '21 at 08:18
  • @Nick.McDermaid yeah i was using ksa but i change it now to Arabic_CI_AI but still same result – Bassem Ahmed Dec 20 '21 at 09:34
  • @AlwaysLearning you are absoultly right in that i just copy past same problem from another thread which i can see brilliant soultion int that thread but unfornatully with mysql i was hopping to find same solution in sql https://stackoverflow.com/questions/43443740/searching-arabic-names-discard-the-differences-between-%d8%a3-%d8%a7-in-mysql/43445429#43445429 – Bassem Ahmed Dec 20 '21 at 09:35
  • I don't personally believe that _AI (accent insensitive) works for Arabic languages in SQL Server, at least not like it does for Latin-based languages. Probably the comments on the op's question in @41686d6564's link, [Search for Arabic text ignoring diacritics, alef hamza differences, and kashida in SQL Server and Oracle](https://dba.stackexchange.com/q/135278/108176), yield the best solution: add persisted computed column(s) to your tables that `translate()` the accented characters to their generic versions and then use those for searching - with the same `translate()` on your search key. – AlwaysLearning Dec 20 '21 at 10:59

0 Answers0