How to get the LENGTH() of string in MySQL8?

by · Published · Updated

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

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, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5. See in the examples.

Note : The Length() OpenGIS spatial function is named ST_Length() in MySQL.

LENGTH() : Syntax

LENGTH ( string );

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]

LENGTH() : Output

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

LENGTH() : Available from

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

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

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

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

For same input both function returns different 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 length('§§§§§'), char_length('§§§§§');
+----------------------+---------------------------+
| length('§§§§§')      | char_length('§§§§§')      |
+----------------------+---------------------------+
|                   10 |                         5 |
+----------------------+---------------------------+
1 row in set (0.02 sec)

LENGTH() Example 3 :

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

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

LENGTH() : Example 4 :  NULL Arguments

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

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

LENGTH() Example 5 : Trailing Blanks

The LENGTH() function counts trailing blanks i.e. 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:

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

LENGTH() Example 6 : Leading Blanks

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

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

 

See all MySQL String functions MySQL 8 String Functions.

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

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

You may also like...