Search code examples
sqlvertica

why this query added extra length upper(trim(LEFT(col_name::varchar, 100)))

select
    distinct upper(trim(LEFT(col_name::varchar, 100))) as col_name from mytable;

Somehow this added extra length since LEFT(col_name::varchar, 100) returns 100 character from left.

And I am getting below error when inserting to another table

ERROR: String of 101 octets is too long for type Varchar(100) If anyone could help me in understanding this behaviour

Solution

  • The LEFT(string, N) function returns N characters from the left side of a string.

    However, those N characters may take more than N bytes (octects), for example accented characters:

    dbadmin=> select left('é', 1);
 left
------
 é

dbadmin=> select octet_length(left('é', 1));
 octet_length
--------------
            2