CODEWITHSUNDEEP
sqloracle-databasespecial-charactersutf
DanielX2010
DanielX2010

Reputation: 1908

string comparing query with chinese chars - Oracle Database

I'm having trouble executing a simple query such as the following

select * from table_name where variabe_name like '在职'

the problem is the chinese chars. Those chars are in the table (I just copied them after doing a select * from table, so the displaying of the chinese chars works just fine) but it doesn't seem to work. When it execute the query, it returns 0 rows.

I' ve also tried 

select * from table_name where variabe_name like '%在职%'

and

select * from table_name where variabe_name = '在职'

But that doesn't work either.

Any clue of what the problem might be?

Thnaks a lot

Upvotes: 1

Views: 4596

Answers (2)

Sly
Sly

Reputation: 36

==> Found solution: put 'N' before the chinese characters, so that they are interpreted as Unicode. Like: where field like N'罐'

Upvotes: 2

Anjan Biswas
Anjan Biswas

Reputation: 7932

 SQL> create table mytbl (data_col varchar2(200));
 Table created
 SQL> insert into mytbl values('在职'); 
 1 row inserted.
 SQL> commit;
 Commit complete.
 SQL> select * from mytbl where data_col like '%在职%';
 DATA_COL                                                                                                                                                                                               
 -----------
 在职 

 SQL> SELECT * FROM nls_database_parameters where parameter='NLS_CHARACTERSET';
 PARAMETER                      VALUE                                  
 ------------------------------ ----------------------------------------
 NLS_CHARACTERSET               AL32UTF8

Your NLS_CHARACTERSET should be set to AL32UTF8. So try

 SQL> ALTER SESSION SET NLS_CHARACTERSET = 'AL32UTF8';

Also make sure that parameter NLS_NCHAR_CHARACTERSET is set to UTF8.

 SQL> ALTER SESSION SET NLS_NCHAR_CHARACTERSET = 'UTF8';

Upvotes: 1

Related Questions