Search code examples
sqloracle-databasecharacter-encodingsubstr

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.

Solution

  • 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.