CODEWITHSUNDEEP
sqloracle-databasecharacter-encodingsubstr
Jeremy
Jeremy

Reputation: 5435

Oracle SQL - Substr not removing enough characters

I am facing a strange issue. I have code like this.

insert into my_table
    substr(my_name,1,199)

The error given is:

value too large for column ... (actual: 246, maximum: 200)

Looking at the largest names in my table, I've got stuff like:" 8"x12&#34 "

Is is possible that some character set stuff is going on, so that when it inserts, the number of characters increases? If so, how do I rememdy that? I've tried explicitly converting to UTF8 before substringing, but that doesn't help at all.

Upvotes: 2

Views: 5371

Answers (3)

Tsvetan Nikolov
Tsvetan Nikolov

Reputation: 135

You could also use substrb:

 insert into my_table
     substrb(my_name,1,199)

This would limit the string to the first 199 bytes rather than characters.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

I would suggest trying:

insert into my_table(col)
    select cast(substr(my_name,1,199) as varchar(200))

(I always include explicit column names in inserts.)

The explicit conversion to a varchar may solve the problem of the engine thinking the string is too long.

Upvotes: 3

bonCodigo
bonCodigo

Reputation: 14361

As per both the above comments, you man want to check the basics. Also if you could perform both substrb, substr on select and watch differences that could be helpful too.

Here is a great article

" Historically database columns which hold alphanumeric data have been defined using the number of bytes they store. This approach was fine as the number of bytes equated to the number of characters when using single-byte character sets.With the increasing use of multi-byte character sets to support globalized databases comes the problem of bytes no longer equating to characters.The VARCHAR2 and CHAR types support two methods of specifying lengths:In bytes: VARCHAR2(10 byte). This will support up to 10 bytes of data, which could be as few as two characters in a multi-byte character sets.In characters: VARCHAR2(10 char). This will support to up 10 characters of data, which could be as much as 40 bytes of information."

Advice

When using a multibyte character set such as UTF8, since your intention is likely to define a column that can in fact store characters of data, you may use :

  • the CHAR modifier in the VARCHAR2/CHAR definition. That is, use of VARCHAR2(80 CHAR), not VARCHAR2(80).

  • the session or system parameter NLS_LENGTH_SEMANTICS to change the default behavior from BYTE to CHAR.

It's not recommended to change this setting at the system level scope but rather an the SESSION scope.

It says,

Two issues, people encounter frequently, are that :

  • when using multibyte character sets, namely that a VARCHAR2(N) doesn't necessarily hold N characters, but rather N bytes.

  • the maximum length in bytes of a VARCHAR2 is 4,000, and in a CHAR it is 2,000:

Upvotes: 2

Related Questions