Calculate string size in UTF-8 when converted from Latin-9 (ISO/IEC 8859-15)

Question

We have a jdbc program which moves data from one database to another.

1. Source database is using Latin9 character set
2. Destination database uses UTF-8 encoding and the size of a column is specified in bytes instead of characters
3. We have converted ddl scripts of source database to equivalent script in destination database keeping the size of the column as-is.
4. In some cases, if there are some special characters, the size of the data after converting to UTF-8 is exceeding the size of the column in destination database causing the jdbc program to fail.

I understand that UTF-8 is variable-width encoding scheme which can take 1-4 bytes per character, given this the worst case solution would be to allocate 4 times the size of a column in destination database.

Is there a better estimate?

Answer 1

Since there's no telling in advance exactly how much a text string will grow, I think that all you can do is a trial run to convert the text to UTF-8, and generate a warning that certain columns need to be increased in size. Any ASCII (unaccented) characters will remain single bytes, and most Latin-9 accented characters will probably be 2 bytes each, but there are some that might be 3. You'd have to look at the Latin-9 and UTF-8 tables to see if any will be 3 or 4 bytes after conversion. Still, you'd have to examine your Latin-9 text to see how much it will grow.

Answer 2

The Euro symbol in Latin-9 will take 3 bytes to represent in utf-8. The ascii characters will only take 1 byte. The remaining 127 characters will take 2 bytes. Depending on what the actual locale is (and what characters are commonly used) an estimate between 1.5x and 2x should be sufficient.