MySQL LENGTH() Function

Introduction

In this chapter, we will learn about the LENGTH() function in MySQL. The LENGTH() function returns the length of a string in bytes. This function is useful for determining the size of text data and for validation purposes. We will cover the syntax for the LENGTH() function, examples of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the LENGTH() function in MySQL is:

SELECT LENGTH(string);

• string: The string whose length you want to determine.

Examples

Let’s go through examples of using the LENGTH() function with various use cases.

1. Find the Length of a Simple String

Example: Find the Length of ‘Hello, World!’

SELECT LENGTH('Hello, World!') AS length;

Output:

2. Using LENGTH() with Table Data

Assuming we have an employees table:

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

INSERT INTO employees (first_name, last_name, email) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com'),
('Priya', 'Singh', 'priya.singh@example.com'),
('Amit', 'Kumar', 'amit.kumar@example.com');

Example: Find the Length of Email Addresses

SELECT email, LENGTH(email) AS email_length
FROM employees;

Output:

3. Using LENGTH() with WHERE Clause

Example: Find Employees with Email Length Greater Than 20

SELECT first_name, last_name, email
FROM employees
WHERE LENGTH(email) > 20;

Output:

Full Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the LENGTH() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

INSERT INTO employees (first_name, last_name, email) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com'),
('Priya', 'Singh', 'priya.singh@example.com'),
('Amit', 'Kumar', 'amit.kumar@example.com');

2. Find the Length of Each Employee’s Full Name

SELECT first_name, last_name, LENGTH(CONCAT(first_name, ' ', last_name)) AS name_length
FROM employees;

Output:

3. Find Employees with Short Last Names

SELECT first_name, last_name, LENGTH(last_name) AS last_name_length
FROM employees
WHERE LENGTH(last_name) < 6;

Output:

Important Considerations

• Byte Length: The LENGTH() function returns the length of the string in bytes, not characters. This is important for multi-byte character sets where the number of bytes may be greater than the number of characters.

• Character Length: If you need to find the length in characters, use the CHAR_LENGTH() function instead of LENGTH().

  SELECT CHAR_LENGTH('Hello, World!') AS char_length;
  

• Performance: The LENGTH() function can impact performance when used on large datasets. Optimize your queries by calculating lengths only when necessary.

Conclusion

The LENGTH() function in MySQL is used for determining the length of a string in bytes. This chapter covered the syntax for using the LENGTH() function, provided examples of its usage, and discussed important considerations. By mastering the LENGTH() function, you can efficiently handle and manipulate text data in your databases.