MySQL LENGTH() function

LENGTH() function

MySQL LENGTH() returns the length of a given string.

This function is useful in -

• String length: The number of characters in a string can be used to determine its length.
• Data validation: LENGTH() validates string length against specific criteria. The length of a username or password can be checked before storing it.
• Data manipulation: LENGTH() is often used for substring extraction or truncation based on a specific length. The number of characters is used to extract or manipulate portions of a string.

Syntax:

LENGTH (str)

Argument:

Syntax Diagram:

MySQL Version: 8.0

MySQL LENGTH(): Basic usage

The following MySQL statement will return the length of the string 'Hello World', which is 11 characters.

Code:

SELECT LENGTH('Hello World');

Output:

LENGTH('Hello World')|
---------------------+
                   11|	  

Example of MySQL LENGTH() function

The following MySQL statement returns the pub_name and length of pub_name from publisher table.

Code:

SELECT pub_name,LENGTH(pub_name) FROM publisher;

Sample table: publisher

Output:

mysql> SELECT pub_name,LENGTH(pub_name) FROM publisher;
+------------------------------+------------------+
| pub_name                     | LENGTH(pub_name) |
+------------------------------+------------------+
| Jex Max Publication          |               19 | 
| BPP Publication              |               15 | 
| New Harrold Publication      |               23 | 
| Ultra Press Inc.             |               16 | 
| Mountain Publication         |               20 | 
| Summer Night Publication     |               24 | 
| Pieterson Grp. of Publishers |               28 | 
| Novel Publisher Ltd.         |               20 | 
+------------------------------+------------------+
8 rows in set (0.03 sec)

Handling multi-byte characters

Code:

SELECT LENGTH('Café');

Output:

LENGTH('Café')|
--------------+
             5|	  

In this example, the LENGTH() function counts the multi-byte character 'é' as 2 characters, resulting in a length of 5 for the string 'Café'.

Using LENGTH() with whitespace

Code:

SELECT LENGTH('   Hello   ');

Output:

LENGTH('   Hello   ')|
---------------------+
                   11|	  

In this example the LENGTH() function counts all characters, including leading and trailing whitespace, resulting in a length of 11 for the string with extra spaces.

Handling NULL values

Code:

SELECT LENGTH(NULL);

Output:

LENGTH(NULL)|
------------+
            |	  

In this example the LENGTH() function returns NULL when applied to a NULL value.

Example of MySQL LENGTH() function with where clause

The following MySQL statement returns the pub_name and length of pub_name from publisher table who have the length of there is more than or equal to 20.

Code:

SELECT pub_name,LENGTH(pub_name) FROM publisher WHERE LENGTH(pub_name)>=20;

Sample table: publisher

Output:

mysql> SELECT pub_name,LENGTH(pub_name) FROM publisher
    -> WHERE LENGTH(pub_name)>=20;
+------------------------------+------------------+
| pub_name                     | LENGTH(pub_name) |
+------------------------------+------------------+
| New Harrold Publication      |               23 | 
| Mountain Publication         |               20 | 
| Summer Night Publication     |               24 | 
| Pieterson Grp. of Publishers |               28 | 
| Novel Publisher Ltd.         |               20 | 
+------------------------------+------------------+
5 rows in set (0.03 sec)

Video Presentation:

All String Functions (Slides presentation)

Previous: LEFT
Next: LIKE