Find String Length in MySQL

MySQL provides functions for determining string lengths in both character count and byte size, which is especially useful for multilingual text and varying character encodings.

In MySQL, finding the length of a string is often necessary for data validation, formatting, and processing tasks. For instance, you may want to check that usernames meet minimum length requirements or format text based on length.


Using the CHAR_LENGTH() and LENGTH() Functions in MySQL

MySQL offers two primary functions to find string lengths:

  • CHAR_LENGTH(): Returns the number of characters in a string, regardless of byte size.
  • LENGTH(): Returns the byte length of a string, which may differ from character length in multi-byte encodings.

Let’s set up an example table and demonstrate how to use these functions with different data types and encodings.


Setting Up an Example Table

To illustrate CHAR_LENGTH() and LENGTH(), we’ll create a sample texts table with fields content and language. These fields will contain text values in different languages and encodings to show how the functions work with various data.

1. Create a new database called sample_data:

</>
Copy
CREATE DATABASE sample_data;

2. Select the sample_data database:

</>
Copy
USE sample_data;

3. Create the texts table with fields content and language:

</>
Copy
CREATE TABLE texts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(255),
    language VARCHAR(50)
);

4. Insert sample data into the texts table:

</>
Copy
INSERT INTO texts (content, language)
VALUES ('Hello World', 'English'),
       ('こんにちは', 'Japanese'),
       ('Hola Mundo', 'Spanish'),
       ('Привет мир', 'Russian');
Find String Length in MySQL - Setup database for examples

With the table set up, we can use the length functions in MySQL on our sample data.


Examples: Finding String Length in MySQL

Let’s explore examples of using CHAR_LENGTH() and LENGTH() in SQL queries to find string lengths.

texts table

1. Finding Character Length with CHAR_LENGTH()

To get the character count of each content value in the texts table:

</>
Copy
SELECT content, CHAR_LENGTH(content) AS character_length
FROM texts;

This query returns the number of characters in each text, regardless of the byte size for multi-byte characters, providing an accurate character count for all languages.

Find String Length in MySQL - Example - Finding Character Length with CHAR_LENGTH()

2. Finding Byte Length with LENGTH()

To calculate the byte size of each content value in the texts table:

</>
Copy
SELECT content, LENGTH(content) AS byte_length
FROM texts;

This query returns the byte size for each text entry. For example, Japanese or Russian characters may occupy more than one byte, resulting in a larger byte length than character count.

Find String Length in MySQL - Example - Finding Byte Length with LENGTH()

3. Using CHAR_LENGTH() and LENGTH() in Conditional Statements

To find entries where the character length is less than 10 characters:

</>
Copy
SELECT content, CHAR_LENGTH(content) AS character_length
FROM texts
WHERE CHAR_LENGTH(content) < 10;

This query filters rows to show only those with fewer than 10 characters, helping identify shorter text entries in the table.

Find String Length in MySQL - Example - Using CHAR_LENGTH() and LENGTH() in Conditional Statements

4. Using LENGTH() to Filter Based on Byte Size

To retrieve rows where the byte size of content is greater than 20 bytes:

</>
Copy
SELECT content, LENGTH(content) AS byte_length
FROM texts
WHERE LENGTH(content) > 10;

This query shows entries where the total byte size exceeds 20 bytes, which may include texts with multi-byte characters or longer content.

Find String Length in MySQL - Example - Using LENGTH() to Filter Based on Byte Size