1

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.

αғsнιη
  • 2,627
  • 2
  • 25
  • 38
  • And you can see a row with that value if you query without the filter? Does your client character set (e.g. from NLS_LANG if this is in SQL\*Plus) support those characters properly? What is the database character set, and is the column `varchar2` or `nvarchar2`? – Alex Poole Jun 23 '16 at 10:54
  • Do you get anything from `select '%تهران%' as a from dual;`? – Wernfried Domscheit Jun 23 '16 at 11:57
  • Seems like what you're doing ought to work. Can you try running the same thing though SQL\*Plus? I suppose it's possible PL/SQL Developer is confused, though that seems unlikely. Maybe check its settings to see if it's specifying a character set. – Alex Poole Jun 23 '16 at 11:58
  • @AlexPoole, updated Q, in SQL*Plus I got same empty result, even I tried to find for only one character like `ا` in Persian but no resul :'( Do you suggest I re-build a new table from my Original table and change the type to `nvarchar2`? If I do this I can get my desired result? – αғsнιη Jun 23 '16 at 13:24
  • No. Hard to tell what's happening. Check your NLS_LANG, and try using `dump()` to check the values in your table are what you think. – Alex Poole Jun 23 '16 at 13:32

2 Answers2

0

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.

αғsнιη
  • 2,627
  • 2
  • 25
  • 38
0
select t1.pname from tableName t1 where t1.address like n'%تهران%'

just add n before ''

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
mehran arbabian
  • 138
  • 1
  • 10