I am looking for how to search a database using Arabic text. In Arabic there are some letters that can be written in different ways but in the results they should all show up if one of them is included in the where clause.
The famous example for this would be:
SELECT * FROM persons WHERE name = "اسامة";
+----+--------------+
| id | name |
+----+--------------+
| 3 | أسامه |
| 4 | أسامة |
| 5 | اسامه |
| 6 | اسَامه |
+----+--------------+
4 rows in set (0.00 sec)
I found a good and probably most performant way to do this by creating a custom collation on MySQL in this article but I have no idea how that is done or if it is possible at all in PostgreSQL.
Other ways that include changing the query itself to use Regex are not useful for my use case.
Can someone please guide me how to do the same