How the OCTET_LENGTH() Function Works in MySQL

In MySQL, the OCTET_LENGTH() function returns the length of a string, measured in bytes.

This function is actually a synonym of LENGTH().

Syntax

The basic syntax goes like this:

OCTET_LENGTH(str)

Where str is the string for which you want the length returned.

Example

SELECT OCTET_LENGTH('Cat');

Result:

+---------------------+
| OCTET_LENGTH('Cat') |
+---------------------+
|                   3 |
+---------------------+

This is the same result that we’d get if we used the CHAR_LENGTH() function. However, the OCTET_LENGTH() function can return different results, depending on the data type.

Data Types

When you query a database, the OCTET_LENGTH() function can return a different result, depending on the data type. Unicode strings return double the number of bytes. UTF-8 strings can vary.

Here’s an example of using UTF-8:

SELECT 
  CHAR_LENGTH(_utf8 '€'),
  OCTET_LENGTH(_utf8 '€');

Results:

+--------------------------+---------------------------+
| CHAR_LENGTH(_utf8 '€')   | OCTET_LENGTH(_utf8 '€')   |
+--------------------------+---------------------------+
|                        1 |                         3 |
+--------------------------+---------------------------+

In this case, the character length is 1, but the octet length is 3 bytes.

In the following example, we query a database. In this case, the ArtistName column uses varchar(255) data type:

SELECT OCTET_LENGTH(ArtistName) 
FROM Artists
WHERE ArtistName = 'Lit';

So the result looks like this:

+--------------------------+
| OCTET_LENGTH(ArtistName) |
+--------------------------+
|                        3 |
+--------------------------+

However, if we change the column to ucs2, each character will be represented by a 2 byte Unicode code, and therefore the result will be different.

To demonstrate, we can alter the table like this:

SELECT OCTET_LENGTH(ArtistName) 
ALTER TABLE Artists 
MODIFY COLUMN ArtistName VARCHAR(255) CHARSET ucs2;

Now if we query it again:

SELECT OCTET_LENGTH(ArtistName) 
FROM Artists
WHERE ArtistName = 'Lit';

Result:

+--------------------------+
| OCTET_LENGTH(ArtistName) |
+--------------------------+
|                        6 |
+--------------------------+

Trailing Blanks

The OCTET_LENGTH() function counts trailing blanks (such as spaces at the end of the string). So if we add a space to the end of the first example, we get the following result:

SELECT LENGTH('Cat ');

Result:

+----------------+
| LENGTH('Cat ') |
+----------------+
|              4 |
+----------------+

Leading Blanks

We get the same result with leading blanks (e.g. spaces at the start of the string):

SELECT LENGTH(' Cat');

Result:

+----------------+
| LENGTH(' Cat') |
+----------------+
|              4 |
+----------------+

Unless of course, we use a function such as TRIM(), RTRIM(), or LTRIM() to trim the whitespace.

Example:

SELECT LENGTH(RTRIM('Cat '));

Result:

+-----------------------+
| LENGTH(RTRIM('Cat ')) |
+-----------------------+
|                     3 |
+-----------------------+

More Examples

Here are some examples of various strings:

SELECT 
  OCTET_LENGTH('Quiet Riot') AS 'Result 1',
  OCTET_LENGTH('') AS 'Result 2',
  OCTET_LENGTH('1234  7') AS 'Result 3',
  OCTET_LENGTH('   ') AS 'Result 4',
  OCTET_LENGTH(TRIM('   ')) AS 'Result 5';

Result:

+----------+----------+----------+----------+----------+
| Result 1 | Result 2 | Result 3 | Result 4 | Result 5 |
+----------+----------+----------+----------+----------+
|       10 |        0 |        7 |        3 |        0 |
+----------+----------+----------+----------+----------+

Note that results 4 and 5 are different because I used the TRIM() function to trim the whitespace from result 5. Seeing as the string only consists of whitespace, the length of the trimmed string is 0.