A declared length such as VARCHAR(255) does not mean the same thing in every database. In MySQL and PostgreSQL the number counts characters. In Oracle it counts bytes unless the column or the session says otherwise. In SQL Server it counts bytes for varchar and byte-pairs for nvarchar, and Microsoft's documentation says that n "never defines numbers of characters that can be stored". Where a column is declared in characters, byte limits can still apply behind it: MySQL's row size and index limits are in bytes.

Comparison at a glance

Database Type The declared length counts Function for characters Function for bytes
MySQL VARCHAR(n) Characters CHAR_LENGTH() LENGTH()
PostgreSQL varchar(n) Characters char_length(), length() octet_length()
Oracle Database VARCHAR2(n) Bytes by default; characters with the CHAR qualifier LENGTH() LENGTHB()
SQL Server varchar(n) Bytes LEN() DATALENGTH()
SQL Server nvarchar(n) Byte-pairs LEN() DATALENGTH()

The table reflects the vendor documentation listed in this page's sources: the MySQL 8.4 manual, the current PostgreSQL documentation (version 18), the Oracle Database 19c manuals and Microsoft Learn. Other versions may differ, so check the manual for the version you run.

In none of these systems does "character" mean a visible character. Where the documentation is specific, it means a code point. A family emoji such as 👨‍👩‍👧‍👦 is one visible character built from 7 code points, 11 UTF-16 code units and 25 UTF-8 bytes, so it uses far more than 1 of any column's allowance. The guide to code points and grapheme clusters explains the difference.

MySQL

The MySQL manual says that CHAR and VARCHAR are "declared with a length that indicates the maximum number of characters you want to store". The number of bytes behind each character depends on the column's character set. For utf8mb4 the manual gives a maximum of four bytes per character.

Bytes come back in through two limits, and through the character set itself, which the next section covers:

  • Row size. The manual says the effective maximum length of a VARCHAR is "subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used".
  • Index keys. For InnoDB tables the index key prefix limit is 3072 bytes with the DYNAMIC or COMPRESSED row format and 767 bytes with REDUNDANT or COMPACT. At four bytes per character that is 768 and 191 characters. The manual uses the same 191 figure in its own example, and it is the likely reason you may have seen VARCHAR(191) in a schema.

MySQL's two length functions are easy to mix up. LENGTH() returns bytes: the manual describes it as "measured in bytes". CHAR_LENGTH() is "measured in code points". OCTET_LENGTH() is a synonym for LENGTH(), and CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

SELECT CHAR_LENGTH('café') AS characters, LENGTH('café') AS bytes;
-- With a utf8mb4 connection, and é typed as the single code point U+00E9:
-- characters = 4, bytes = 5

Because CHAR_LENGTH() counts code points, the family emoji above measures 7 with it, not 1.

The utf8 and utf8mb4 trap

In MySQL, the name utf8 does not mean full UTF-8. The manual states that utf8 has been used "as an alias for the utf8mb3 character set", and that this usage is deprecated. utf8mb3 needs at most three bytes per character, which covers code points up to U+FFFF and nothing above.

Most emoji are above U+FFFF. The manual is blunt about what that means: for such a character, "utf8mb4 requires four bytes to store it, whereas utf8mb3 cannot store the character at all".

So a column can be declared with plenty of room and still be unable to hold 🙂. If a field has to accept emoji, check which character set the column actually uses, not just its declared length. This guide does not cover changing the character set of an existing table. That is a migration which affects index sizes and row sizes, and it needs its own plan and testing.

PostgreSQL

The PostgreSQL documentation says that character varying(n) and character(n) "can store strings up to n characters (not bytes) in length".

The functions match that. length() and char_length() return the number of characters in a string, and octet_length() returns the number of bytes. The documentation's own example gives 4 characters and 5 bytes for a four-letter name ending in é, "if server encoding is UTF8".

SELECT char_length('café') AS characters, octet_length('café') AS bytes;
-- In a UTF8 database, and é typed as the single code point U+00E9:
-- characters = 4, bytes = 5

The two pages cited here do not go on to define "character" in Unicode terms. Before relying on a limit for text that may contain emoji or combining accents, run the same query on a sample of that text and see what your server returns.

Oracle Database

Oracle lets each column choose. In VARCHAR2(n BYTE) the length is in bytes, and in VARCHAR2(n CHAR) it is in characters. The SQL reference describes character semantics as the maximum number of "code points in the database character set" that the column can hold.

The catch is the default. With no qualifier, the reference says the NLS_LENGTH_SEMANTICS parameter "of the session creating the column defines the length semantics", and the documented default for that parameter is BYTE. A column written as plain VARCHAR2(50) is therefore 50 bytes on a default setup, and the same statement can produce a different column in a session configured for CHAR.

Two more points from the 19c manuals:

  • A byte ceiling applies even with CHAR semantics. The maximum size of a VARCHAR2 is 4000 bytes, or 32767 bytes when MAX_STRING_SIZE is EXTENDED. In the reference's words, the size in characters "is a length constraint, not guaranteed capacity".
  • NCHAR, NVARCHAR2, CLOB and NCLOB columns "are always character-based".

To measure, LENGTH counts characters as defined by the input character set and LENGTHB counts bytes.

SQL Server

SQL Server is the system where the declared length is never a character count.

For varchar(n), n is the string length in bytes, from 0 to 8,000. Starting with SQL Server 2019 a varchar column can use a UTF-8 collation, and then, as the documentation puts it, characters outside the ASCII range use "two or more bytes" each.

For nvarchar(n), n is the string length in byte-pairs, from 0 to 4,000. A byte-pair is one 16-bit unit, the same unit that JavaScript's length counts. Characters above U+FFFF, which the documentation calls supplementary characters, "use two byte-pairs (also called surrogate pairs)".

Some arithmetic shows the effect:

Column English letters that fit 🙂 that fit
nvarchar(10) 10 5
varchar(10) with a UTF-8 collation 10 2

Collation matters in a second way. The documentation says that when a collation without supplementary-character support is specified, nchar and nvarchar "store only the subset of character data supported by the UCS-2 character encoding".

To measure, LEN() returns the number of characters "excluding trailing spaces", and DATALENGTH() returns the number of bytes. Microsoft notes that with a supplementary-character (SC) collation LEN() counts a surrogate pair as a single character, so its answer for an emoji depends on the collation as well.

Validate in the unit the column uses

A form that allows "50 characters" while the column behind it allows 50 bytes will accept input that does not fit in the column. The fix is to check input in the same unit as the storage, before the write.

The column counts Measure the same thing in JavaScript
UTF-8 bytes new TextEncoder().encode(text).length
Code points Array.from(text).length
Byte-pairs (UTF-16 code units) text.length

The guide to JavaScript string length explains each line, and UTF-8 bytes vs characters covers byte counting in other languages.

If the limit shown to users is in visible characters, that is a fourth unit and no column type enforces it. Check it in the application, and size the column so that it is not the first thing to fail. There is no fixed ratio between the two: one visible character can be many code points and many bytes.

Test with text that is not plain English

A limit that has only been tested with aaaa… has not been tested. Before trusting a field, try each of these on a test system: at exactly the limit, at one more, and well over.

  • Plain English letters, as a baseline.
  • Two-byte letters such as é or ü.
  • Three-byte characters such as € or 日.
  • A four-byte emoji such as 🙂.
  • A multi-code-point emoji such as 👍🏽 or 👨‍👩‍👧‍👦.
  • An accented word in its decomposed form, where é is stored as e plus a combining accent.
  • Text with trailing spaces.

For each case, note three things: whether the application accepted it, whether the database stored it, and whether what comes back is identical to what went in. You can prepare the samples in the Character Counter, where Advanced statistics shows code points, UTF-16 code units and UTF-8 bytes for whatever you paste.

Decide what happens on overflow

Make the behavior for over-long input a decision, not an accident of configuration.

  • Reject with a clear message when the content matters to the user, such as a name, an address or anything they wrote themselves. Tell them the limit in terms they can act on.
  • Truncate deliberately only where losing the end is acceptable, and cut on a visible character boundary so that an emoji or an accented letter is never left broken. Never cut in the middle of a multi-byte sequence.
  • Find out what the database itself does with a value that is too long, in the configuration you actually run. Do this on a test system, and do not leave it as the only line of defense.

Whichever you choose, apply it in one place and report the same limit to the user that the storage enforces.