How to compare two arabic string in oracle?

Question

I have two strings:

a = 'على';
b = 'علي';

or

a = 'محمد';
b ='محــــــــــمد';

or

a= 'بُنْ'
b='بن'

a= 'يسرا'
b='يسرى'

and I already have data in database

What I have tried so far:

How can I compare them and return true? i cant collect all cases

Possible duplicate of [comparing two strings in SQL Server](https://stackoverflow.com/questions/6623802/comparing-two-strings-in-sql-server) — Anas Alweish, May 28 '19 at 13:31
You haven''t explained what happened when you ran the query. I suppose you got an error, because `STRCMP` doesn't exist in the Oracle DBMS? Where do you get the values from? — Thorsten Kettner, May 28 '19 at 19:24
yes, STRCMP doesn't exist in the Oracle DBMS and get an error — Eman mohamed, May 29 '19 at 08:22

score 2 · Accepted Answer · answered May 28 '19 at 19:01

2

SELECT CASE 
    WHEN UTL_MATCH.EDIT_DISTANCE_SIMILARITY('على', 'علي') > 75 
    THEN 'Match' ELSE 'No Match' END as "Match Status" 
FROM dual;

answered May 28 '19 at 19:01

gmiley

when use it return different but two this name match SELECT CASE WHEN UTL_MATCH.EDIT_DISTANCE_SIMILARITY('محمد', 'محمــد') > 75 THEN 'Match' ELSE 'No Match' END as "Match Status" FROM dual; – Eman mohamed May 29 '19 at 07:10
You can adjust the value of "sameness" by increasing or decreasing the `75`. It is basically a percentage of sameness. – gmiley May 29 '19 at 10:38
I use it but I when i write with formatting Arabic word the result not true. replace Formation in the Arabic language with empty in pl sql. I try select TRANSLATE('عــــــلًــــــي', 'ُ', '') from dual; but return null. – Eman mohamed May 30 '19 at 07:45
@Emanmohamed Is your database setup to use an Arabic character set, or is it using a Western European char set (WE8ISO8859P1)? You can find out what you are currently using with the following statement: `select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');` If you are not using an Arabic char set (AR8ISO8859P6), you will run into difficulties using a lot of these string/word/phrase comparison functions/techniques. To change your char set, look into: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch11charsetmig.htm#g1011430 – gmiley May 30 '19 at 10:08

score 0 · Answer 2 · answered May 28 '19 at 14:32

0

SELECT * FROM DUAL
WHERE a = b;

A row will be returned if they are equal, no rows if not equal.

answered May 28 '19 at 14:32

Based

when compare a and b will find a not equal b but a = 'يسرا ' and b= 'يسري ' are the same but different syntax – Eman mohamed May 29 '19 at 07:27

score 0 · Answer 3 · answered May 28 '19 at 19:25

0

You can either use the comparision operator = or Oracle's SQL function DECODE in order to compare values.

select case when 'على' = 'علي' then 'same' else 'different' end from dual;

select decode('على' ,'علي', 'same', 'different') from dual;

answered May 28 '19 at 19:25

Thorsten Kettner

3 Answers3