CODEWITHSUNDEEP
sqlstringoracle-databasecharactercjk
Walker
Walker

Reputation: 1307

Oracle SQL load table columns having special characters

I am loading and inserting data into an Oracle database. When I encounter special characters that look like Chinese characters, I am getting an error like row rejected because maximum size of column was exceeded. I am not getting this error for rows which have English characters which appear to be of same length for same column. I am using SUBSTR and TRIM function but it is not working. How can I determine whether the length of a string which is in Chinese exceeds column size?

Upvotes: 1

Views: 3603

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

if your columns are defined as VARCHAR2(XX) [for example VARCHAR2(20)], you will receive an error if you try to insert a string that is more than XX bytes long.

The function SUBSTR calculates length in number of characters, not bytes. To select a substring in bytes, use the function SUBSTRB.

SQL> select substr('ЙЖ', 1, 2) from dual;

SUBSTR('ЙЖ',1,2)
------------------
ЙЖ

SQL> select substrb('ЙЖ', 1, 2) from dual;

SUBSTRB('ЙЖ',1,2)
-------------------
Й

Edit: As suggested by Adam, you can use character arithmetics if you define your columns and variables as VARCHAR2 (XX CHAR). In that case your columns will be able to store XX characters, in all character sets (up to a maximum of 4000 bytes if you store it in a table).

Upvotes: 3

Related Questions