SQL tables using VARCHAR with UTF8 (relative to the length of several bytes)

Question

Like in Oracle VARCHAR( 60 CHAR ) I would like to specify a varchar field with a variable length depending on the characters inserted.

eg:

 create table X (text varchar(3)) insert into X (text) VALUES ('äöü') 

It should be possible (with UTF8 as the default encoding of the database).

In DB2, I got this error: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001 (Character data, correct truncation occurred, for example, the update or insert value is a row that is too long for the column, or the datetime value cannot be assigned host variable because it is too small.)

I am looking for solutions for DB2, MsSql, MySql, Hypersonic.

Answer 1

DB2

DB2 documentation says:

In multi-byte UTF-8 encoding, each ASCII character is one byte, but non-ASCII characters take two to four bytes each. This must be considered when defining CHAR fields. Depending on the ratio of ASCII characters to non-ASCII, a n-byte CHAR field can contain n / 4 to n characters.

This means that with the DB2 database you cannot do what you requested.

---

Mysql

MySql documentation says:

UTF-8 (Unicode Conversion Format with 8-bit Units) is an alternative way to store Unicode data. It is implemented in accordance with RFC 3629, which describes encoding sequences that take from one to four bytes. Currently, MySQL support for UTF-8 does not include four-byte sequences. (The older UTF-8 encoding standard, RFC 2279, describes UTF-8 sequences that take one to six bytes. RFC 3629 makes RFC 2279 obsolete, so sequences with five and six bytes are no longer used.)

This means that with the MySql database, you can use VARCHAR(3) CHARACTER SET utf8 as the column definition to get what you requested.

Answer 2

For SQL Server, you will need to use NVARCHAR (unicode). Hope someone can fool others!

Answer 3

For HSQLDB (Hypersonic), VARCHAR (3) works as the default encoding of UTF16.