How to get length of a string column in Athena?

Question

How to get length of a VARCHAR or STRING column in AWS Athena? The AWS Documentation doesn't give any information on a length function, which works equivalent to the LEN() function in Redshift.

Answer 1

The length() function returns the size in characters. This can be different in the case of unicode strings with multi-byte characters. I had issues inserting such data from a delta table into a different DB that was using fixed length varchar columns: even though the length was within the limit of the other DB, the insert still failed with data being to long for the respective columns.

The answer here is to use length(cast(column_name as varbinary)), which gives the length in bytes; this should then work when copying the data in the context described above into a DB that uses bytes as string length counter.

Answer 2

The Presto's length() functions works for getting the size of a STRING/VARCHAR column.

Usage : length(column_name)