CODEWITHSUNDEEP
sqloracle-databaseencodingsqlplus
petetheheat
petetheheat

Reputation: 7

SQL Using Chinese Letters in Statement

I've been searching a lot this week but I haven't yet found a solution. Thanks for your help in advance!

I am trying to automate a SQL Query via running it as batch file over sqlplus in Windows cmd. I am quite limited to this tool as sql cl is not supported at the company I work for. Overall sqlplus works fine - it reads in the query and gives output EXCEPT it obviously skips the part with Chinese letters.

In SQL Developer the whole query works.

The purpose is to return the number of elements that are located at a highway, so part of the statement is to search in the addr_street field for highway/autobahn/...:

AND ((addr_street Like '%Autobahn%'            
   OR addr_street Like '%Autovia%'            
   OR addr_street Like '%Autopista%'          
   OR addr_street Like '%Interstate%'        
   OR addr_street Like '%Highway%'
   OR addr_street Like '%Autostrada%'
   OR addr_street Like '%公路%'

.....

How can I rewrite the part with the Chinese letters as ASCII Code so that sqlplus on the cmd is able to read the file in?

I tried to use the unistr function - the query in SQL Developer did not return an error but does not return any additional result at several tries with different input. 

AND ((addr_street Like '%Autobahn%'            
   OR addr_street Like '%Autovia%'            
   OR addr_street Like '%Autopista%'          
   OR addr_street Like '%Interstate%'        
   OR addr_street Like '%Highway%'
   OR addr_street Like '%Autostrada%'
   OR addr_street Like unistr('\0631\06CC\062D\0627\00B7\00AF')

So my question is simple:

How can I rewrite 

OR addr_street Like '%公路%'

to NOT use the Chinese symbols and get the same result? Would you use another tool/approach than sqlplus?

Upvotes: 0

Views: 177

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Unistr seems to work here, let's use asciistr at first:

select asciistr(n'公路') from dual   --> result:  \516C\8DEF

and use these values in query:

with t(addr_street) as (select 'x公路q' from dual)
select * from t where addr_street like '%' || unistr('\516C\8DEF') || '%'

Row found.

Upvotes: 1

Marcus
Marcus

Reputation: 3869

Use single quotes, rather than double quotes, to create a text literal and for a NVARCHAR2/NCHAR text literal you need to prefix it with N

CREATE TABLE table_name ( value NVARCHAR2(20) );
INSERT INTO table_name VALUES (N'玄真');

Query 

SELECT * FROM table_name

select asciistr(N'玄真') from table_name --this is optional if you already insert value with 'N'

Result

| VALUE |
|-------|
|    玄真 |

Upvotes: 0

Related Questions