Ways to get the OCTET_LENGTH() of string in MySQL8?

by · Published · Updated

This function helps in getting the length of a string in bytes.

OCTET_LENGTH() functions returns the length of a string in bytes. A multibyte character counts as a multiple bytes. This means that for a string containing five 2-byte characters, OCTET_LENGTH()  and LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

Note : It is similar to LENGTH() function in MySQL.

OCTET_LENGTH() : Syntax

OCTET_LENGTH ( string );

OCTET_LENGTH() : Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|lef|lef|left”]
Name, Required /Optional, Value Type, Description
string , Required, String , The input string to count the length.
[/table]

OCTET_LENGTH() : Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns,
Number, returns a length of characters in given string.
[/table]

OCTET_LENGTH() : Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]

OCTET_LENGTH()  Example 1 : Below example will return length of string and LENGTH() also return the same value.

mysql> SELECT OCTET_LENGTH('tutorialmines'),LENGTH('tutorialmines');
+-------------------------------+-------------------------+
| OCTET_LENGTH('tutorialmines') | LENGTH('tutorialmines') |
+-------------------------------+-------------------------+
|                            13 |                      13 |
+-------------------------------+-------------------------+
1 row in set (0.00 sec)

OCTET_LENGTH()  Example 2 : Below example will return length of characters in bytes.

For same input both function returns same values because the section sign, § is  a typographical glyph for referencing individually numbered sections of a document; it is frequently used when citing sections of a legal code. It is a multibyte characters.

mysql> SELECT OCTET_LENGTH('§§§§§'), LENGTH('§§§§§');
+----------------------------+----------------------+
| OCTET_LENGTH('§§§§§')      | LENGTH('§§§§§')      |
+----------------------------+----------------------+
|                         10 |                   10 |
+----------------------------+----------------------+
1 row in set (0.00 sec)

OCTET_LENGTH() Example 3 :

For same input both function returns same values because the Euro sign € is a multibyte characters. The Euro sign uses 3 bytes.

mysql> SELECT OCTET_LENGTH('€'), LENGTH('€');
+---------------------+---------------+
| OCTET_LENGTH('€')   | LENGTH('€')   |
+---------------------+---------------+
|                   3 |             3 |
+---------------------+---------------+
1 row in set (0.00 sec)

OCTET_LENGTH() Example 4 :  NULL Arguments

It will return NULL, if the argument is NULL. Lets see the below example.

mysql> SELECT OCTET_LENGTH(NULL);
+--------------------+
| OCTET_LENGTH(NULL) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)

OCTET_LENGTH() Example 5 : Trailing Blanks and leading blanks

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

mysql> SELECT OCTET_LENGTH('tutorialmines '),OCTET_LENGTH(' tutorialmines');     
+--------------------------------+--------------------------------+
| OCTET_LENGTH('tutorialmines ') | OCTET_LENGTH(' tutorialmines') |
+--------------------------------+--------------------------------+
|                             14 |                             14 |
+--------------------------------+--------------------------------+
1 row in set (0.00 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles :LENGTH(), OCT(), BIT_LENGTH(), CHAR(), CHARACTER_LENGTH(), CHAR_LENGTH(), TRIM(), RTRIM(), RIGHT().

PHP Related articles : STRLEN(), SUBSTR_COUNT(), COUNT_CHARS(), STRSTR() ,STRCSPN() , STRCHR(), SUBSTR(), CHR().

You may also like...