2

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.

enter image description here

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

James Z
  • 12,209
  • 10
  • 24
  • 44
Vipul Kumar
  • 67
  • 3
  • 11
  • This post might help "What are all the Japanese whitespace characters?" https://stackoverflow.com/questions/4300980/what-are-all-the-japanese-whitespace-characters – Aman Singh Rajpoot Apr 27 '21 at 18:48

1 Answers1

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
Aman Singh Rajpoot
  • 1,451
  • 6
  • 26
  • I tried this but this is also not working. I have used "regexp_replace(:field_name, '[[:space:]]+$', '')" in my control file. This gives me the expected result. But I'm not sure if this tampers the actual data or not. – Vipul Kumar Apr 28 '21 at 08:12
  • replace will replace all the matching substring In the given regex. Can you try the regular expression with the trim function, trim function will only trim the leading and trailing spaces. – Aman Singh Rajpoot Apr 28 '21 at 08:29
  • Yes, the '$' at the end means the end. So, it works fine for removing trailing spaces but leading spaces will not be trimmed. The trim function is not removing any space(trailing or leading). – Vipul Kumar Apr 28 '21 at 09:30