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:
Name | Description |
---|---|
str | A string whose length is to be returned. |
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:
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook