Arabic SQL query (on Oracle DB) returns empty result

Question

I have this query (that runs on Oracle 10g database):

SELECT ge.*, ge.concept AS glossarypivot 
FROM s_glossary_entries ge 
WHERE (ge.glossaryid = '161' OR ge.sourceglossaryid = '161') 
AND (ge.approved != 0 OR ge.userid = 361)  
AND concept like 'م%'  ORDER BY ge.concept 

The query must display all words that begin with the arabic letter "م" but unfortunately, it returns empty result ..

However, if I run the same query on the same database which runs on MYSQL, it works well and displays the correct result ..

and also, if I run the same query with an english letter (m), like this:

SELECT ge.*, ge.concept AS glossarypivot 
FROM s_glossary_entries ge 
WHERE (ge.glossaryid = '161' OR ge.sourceglossaryid = '161') 
AND (ge.approved != 0 OR ge.userid = 361)  
AND concept like 'm%'  ORDER BY ge.concept 

it displays result correctly and not empty !!

What should I do in order to get this query working the right way on oracle 10 database?

P.S. the oracle database character set is : "AL32UTF8"

thank you so much in advance

Answer 1

Sure that this works in MySQL? I would do this part:

AND concept = 'م'

like this:

AND concept LIKE 'م%'

or because it's arabic and the first char is the right one's like this:

AND concept LIKE '%م'

But i have no idea if Oracle even have LIKE, i never worked with Oracle.

Answer 2

if I put a UTF8 character : " ظ… " instead of the arabic character "م" it will work on oracle ...

Answer 3

The obvious question is, do you have matching data.

You can use SELECT DUMP(concept), DUMP('م') FROM ... to see the bytes that actually form the value. My database gives me 217/133. I believe there are some characters which can have different bytes in UTF-8 but the same physical appearance, though I couldn't say whether this is one of them.

Also, consult the Globalization guide.

Answer 4

i thjink its a mismatch in your oracle client codepage. it should be defined in the same character set as the database, otherwise there will be some character conversion.