SQL tables using VARCHAR with UTF8 (with respect to multi byte character length)

Question

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

for example:

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

Should be possible (with UTF8 as the default charset of the database).

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

I'm looking for solutions for DB2, MsSql, MySql, Hypersonic.

Answer 1

For SQL Server, you'd need to use NVARCHAR (unicode). Hopefully someone can chip in with the others!

Answer 2

DB2

The DB2 documentation says:

In multibyte UTF-8 encoding, each ASCII character is one byte, but non-ASCII characters take two to four bytes each. This should be taken into account when defining CHAR fields. Depending on the ratio of ASCII to non-ASCII characters, a CHAR field of size n bytes can contain anywhere from n/4 to n characters.

This means with a DB2 database you can't do what you asked for .

---

MySql

The MySql documentation says:

UTF-8 (Unicode Transformation Format with 8-bit units) is an alternative way to store Unicode data. It is implemented according to 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. (An older standard for UTF-8 encoding, RFC 2279, describes UTF-8 sequences that take from one to six bytes. RFC 3629 renders RFC 2279 obsolete; for this reason, sequences with five and six bytes are no longer used.)

This means with a MySql database you can use VARCHAR(3) CHARACTER SET utf8 as your column definition to get what you asked for.