I'm usig Oracle 11g to result based on Persian serach match from DB but result I'm getting is nothing, would you please assist how can I search for a result that part of a field name "address" matched with "تهران", thanks
Below query give me no result!
select t1.pname from tableName t1 where t1.address like '%تهران%'
Edit:
Type of column address is varchar2
, my DB characterset is AL32UTF8
, and I'm on PL/SQL Developer GUI, and running below query gives me %?????%
as result.
select '%تهران%' as a from dual;
I tried the same in SQL*Plus, but no luck and same empty result.
so that I founded that Oracle has UNISTR
function that it ...
... takes as its argument a text literal or an expression that resolves to character data and returns it in the national character set. The national character set of the database can be either AL16UTF16 or UTF8.
UNISTR
provides support for Unicode string literals by letting you specify the Unicode encoding value of characters in the string. ...
and I did use UNISTR
function as following after I extracted my keyword's encoding that I should search for it in Unicode code converter:
SELECT t1.pname FROM tableName t1 WHERE t1.address
LIKE (SELECT UNISTR('%\062A\0647\0631\0627\0646%') FROM DUAL);
note that %
s above are actual literal %
added to match keyword like in '%تهران%'
mode.