How to trim whitespaces from Japanese characters while loading data into DB through SQL Loader?

Question

I want to load records into Oracle database through SQL Loader. Everything is working fine except for the Japanese characters are not being trimmed and whitespaces are inserted into the DB. Due to this issue, I have to use StringUtils.stripToEmpty() function in my Java code to get the trimmed value. I want the SQL Loader to trim the whitespaces for me. Is it possible? If yes then please suggest the solution to this.

I have used this in my control file:

[Field_name] "trim(:[Field_name])"

Also, FYI, my Control file has charset specified:

LOAD DATA CHARACTERSET JA16SJIS
APPEND INTO TABLE "[table_name]" fields terminated by '\t' trailing nullcols

Here is the image of SQL Developer DB. And I'm using NVARCHAR2(40 CHAR) as the datatype for this column.

Can't show the field and table names. Any help would be appreciated. Thanks!

Answer 1

Maybe it is not space.

Try this

SELECT trim(chr(14909568) FROM '　ユーザー名' ) FROM dual;

This is space

SELECT ascii(' ') FROM dual;

ASCII('')
---------
32

And the first char in string '　ユーザー名' is

SELECT ascii('　') from dual;

ASCII('')
---------
14909568