MySQL get data by string length

This article will see how to get rows from a MySQL table based on the string length() function. We will be illustrating the concept with various examples.

length(str) function in MySQL returns the length of the string(str) passed in as parameter. Let us get started by creating the sample data. We will be creating a table, employee_name_details, followed by inserting a few rows into it.

# create the table employee_name_details
CREATE TABLE employee_name_details(
  emp_id int  NOT NULL AUTO_INCREMENT,
  emp_firstName varchar(255) DEFAULT NULL,
  emp_lastName varchar(255) DEFAULT NULL,
  primary key(emp_id)
);
# insert rows to the table employee_name_details
INSERT INTO employee_name_details (emp_id,emp_firstName,emp_lastName) 
 VALUES(1,"Henry","Smith"),
 (2,"Richa","Johnson"),
 (3,"Veronica","Brown"),
 (4,"George","Jones"),
 (5,"Harsh","Garcia"),
 (6,"William","Jones"),
 (7,"Rebecca","Miller"),
 (8,"Paul","Davis"),
 (9,"Pualou","Miller");

Execute select * to view the data in the employee_name_details table.

SELECT * FROM employee_name_details;

Output:-

Example1: Get all the rows from employee_name_details where the length of emp_firstName is equal to 7.

SELECT * FROM employee_name_details WHERE length(emp_firstName)= 7;

Output:-

Output in image_2 shows that two rows are returned with the length of emp_firstName as 7.

Example2: Get all the rows from employee_name_details where the length of emp_firstName is less than 5.

SELECT * FROM employee_name_details WHERE length(emp_firstName) < 5;

Output:-

Output in image_3 shows that a single row is returned with the length of emp_firstName less than 5.

Example3: Get all the rows from employee_name_details where the length of emp_firstName is greater than the length of emp_lastName.

SELECT * FROM employee_name_details WHERE length(emp_firstName)  >  length(emp_lastName);

Output:-

Output in image_4 shows that four rows are returned with the length of emp_firstName greater than the length of emp_lastName.

Example4: Get all the rows from employee_name_details ordered by full name(concatenated values of employee first name and last name) of employees

SELECT 
    CONCAT(emp_firstName, ' ', emp_lastName) AS full_name
FROM
    employee_name_details
ORDER BY LENGTH(full_name);

Output:-

Output in image_5 shows that nine rows are returned in the ascending order of full name (concatenated value of emp_firstName and emp_lastName)

READ MORE:

• Mysql update set multiple rows
• Mysql update column with value from another table
• MySQL Update with Inner Join
• MySQL Insert with Join
• MySQL: Insert in a loop

We hope this article helped you with getting data from MySQL tables using the length() function. Good Luck!!!