MySQL LENGTH() function

Last update on August 03 2024 12:25:03 (UTC/GMT +8 hours)

LENGTH() function

The MySQL LENGTH() function is crucial for various operations involving string manipulation and data validation. MySQL LENGTH() returns the length (the number of bytes) in a string, which is essential in scenarios where you need to determine the storage requirements, validate user input, or manipulate strings based on their length.

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.

Returns:

The length of the string in bytes.

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 the length of the string 'Hello World'
SELECT LENGTH('Hello World');

Explanation:

The SQL query uses the LENGTH() function to return the number of characters in the string 'Hello World', which is 11 characters.

Output:

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

Using LENGTH() with Different Character Sets:

Code:


-- Select the length of the string 'Café' when converted to utf8mb4 encoding
SELECT LENGTH(CONVERT('Café' USING utf8mb4)) AS utf8mb4_length,
       -- Select the length of the string 'Café' when converted to latin1 encoding
       LENGTH(CONVERT('Café' USING latin1)) AS latin1_length;

Explanation:

The SQL query calculates the length of the string 'Café' using two different character encodings: utf8mb4 and latin1.

The LENGTH() function returns the number of bytes in the string for each encoding, which helps to understand how different encodings represent multi-byte characters.

Output:

utf8mb4_length|latin1_length|
--------------+-------------+
             5|            4|

This example shows how LENGTH() behaves differently with various character sets.

Validating Input Length:

Sample table: publisher

Code:


-- Select the column 'pub_name' from the 'publisher' table
SELECT pub_name
-- Specify the condition that the length of 'pub_name' should be between 15 and 30 characters inclusive
FROM publisher
WHERE LENGTH(pub_name) BETWEEN 15 AND 30;

Explanation:

This SQL query retrieves the names of publishers from the publisher table where the length of the publisher's name is between 15 and 30 characters inclusive.

The LENGTH() function is used to determine the length of each publisher's name.

Output:

pub_name                     |
-----------------------------+
Jex Max Publication          |
BPP Publication              |
New Harrold Publication      |
Ultra Press Inc.             |
Mountain Publication         |
Summer Night Publication     |
Pieterson Grp. of Publishers |
Novel Publisher Ltd.         |

This query checks if the username length is within the specified range.

Trimming Strings Before Measuring Length:

Code:


-- Select the length of the string 'Hello World' after removing leading and trailing whitespace
SELECT LENGTH(TRIM('   Hello World   '));

Explanation:

This SQL query calculates the length of the string 'Hello World' after removing any leading and trailing whitespace.

The TRIM() function removes the extra spaces, and the LENGTH() function returns the length of the resulting string.

Output:

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

This ensures that leading and trailing spaces are removed before measuring the length.

Comparison with CHAR_LENGTH():

The LENGTH() function returns the length of a string in bytes, whereas CHAR_LENGTH() returns the length in characters. This distinction is important when dealing with multi-byte characters.

Code:


-- Select the length in bytes of the string 'Café'
SELECT LENGTH('Café') AS byte_length,
-- Select the length in characters of the string 'Café'
       CHAR_LENGTH('Café') AS char_length;

Explanation:

This SQL query calculates both the byte length and the character length of the string 'Café'.

The LENGTH() function returns the number of bytes, while the CHAR_LENGTH() function returns the number of characters.

This distinction is important for multi-byte characters like 'é', which may occupy more than one byte.

Output:

byte_length|char_length|
-----------+-----------+
          5|          4|

MySQL LENGTH() function with WHERE clause

Sample table: publisher

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

Code:


-- Select the column 'pub_name' from the 'publisher' table
SELECT pub_name,
-- Calculate the length of the string in 'pub_name' for each row in the 'publisher' table
       LENGTH(pub_name) 
-- From the 'publisher' table
FROM publisher;

Explanation:

This SQL query retrieves the publisher names (pub_name) and their corresponding string lengths from the publisher table.

The LENGTH() function is used to calculate the number of characters in each pub_name.

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:


-- Calculate the length of the string 'Café'
SELECT LENGTH('Café');

Explanation:

This SQL query returns the number of bytes used to store the string 'Café' in the database.

The LENGTH() function measures the length in bytes, which may differ from the number of characters depending on the character encoding used.

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:


-- Calculate the length of the string '   Hello   ' including leading and trailing spaces
SELECT LENGTH('   Hello   ');

Explanation:

This SQL query returns the total number of bytes in the string ' Hello ', including all leading and trailing spaces.

The LENGTH() function counts all characters, including whitespace.

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:


-- Attempt to calculate the length of a NULL value
SELECT LENGTH(NULL);

Explanation:

This SQL query returns NULL because the LENGTH() function does not handle NULL values.

When applied to NULL, the function itself results in NULL.

Output:

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

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

MySQL LENGTH() function with comparison operator:

Sample table: publisher

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 the pub_name and its length from the publisher table
-- where the length of pub_name is greater than or equal to 20
SELECT pub_name, LENGTH(pub_name)
FROM publisher
WHERE LENGTH(pub_name) >= 20;

Explanation:

This query retrieves the names of publishers (pub_name) and their corresponding lengths from the publisher table, but only for those publishers whose names have a length of 20 or more characters.

The LENGTH() function is used both to determine the length of each pub_name and to filter the results based on this length.

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)

Performance Considerations:

Using LENGTH() on large datasets can impact performance, especially if used in WHERE clauses. It's recommended to index columns frequently checked for length or to use LENGTH() in conjunction with LIMIT to minimize performance overhead.

Video Presentation:

All String Functions (Slides presentation)

Previous: LEFT
Next: LIKE