LoaiMd LoaiMd - 10 months ago 43
SQL Question

SQL Like operator do not return data with Arabic characters

I have a query statement that uses "LIKE" operator to retrieve search result from Oracle database that contains Arabic & English data. It works like a charm with English characters after the "LIKE", but it retrieves 0 records with Arabic characters.

here is simplified code of mine:

String whereSt = " WHERE Arb_NAME LIKE '%محمد' "; //Generated from caller method
String selectSt = " Select * from Users " + whereSt;
DynamicDBTable dDBT = new DynamicDBTable(this.dbConn, TableSpace.Users);
DataSet result = dDBT.FetchData();

the strange thing that When I copy the (selectST) to the SQL Developer it works well with both Arabic and English.

any help will be appreciated.

Answer Source

Oracle Data Provider inherits character set from NLS_LANG value. Set your NLS_LANG value to a character set which supports Arabic characters, e.g. NLS_LANG=ARABIC_BAHRAIN.WE8MSWIN1256 or even better ARABIC_BAHRAIN.AL32UTF8

You can set NLS_LANG either as environment variable or in your Registry at HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 32-bit Client), resp. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 64-bit Client).

If NLS_LANG value it not set at all then Oracle defaults it to AMERICAN_AMERICA.US7ASCII, i.e. no support for Arabic characters.

SQL Developer uses JDBC driver to connect to Oracle but JDBC does not use NLS_LANG settings, that's the reason why it works on SQL Developer. See Database JDBC Developer's Guide - Globalization Support

Starting from Oracle Database 10g, the NLS_LANG variable is no longer part of the JDBC globalization mechanism. The JDBC driver does not check NLS environment. So, setting it has no effect.

Apart from that you should prefer bind variables instead of hard-coded strings in WHERE condition.