Admin
In this article, you will see everything about How to check the length of String in MySQL with the help of multiple string functions offered by the MySQL server.
In real-life projects, Most of the time our requirement is to get the length of the string column in MySQL to perform some operations based on the length of the string columns, In that situation, we can go with these string functions which we are going to learn throughout this article.
Headings of Contents
MySQL CHAR_LENGTH() Function
MySQL CHAR_LENGTH() is a string function which means it takes only a string value as a parameter and returns its length. The CHAR_LENGTH() functions return NULL if the given string is NULL.
Syntax
SELECT CHAR_LENGTH(string) FROM Source;
Parameter
The CHAR_LENGTH() function takes a string as a parameter. String parameters can be anything like a hardcoded string, or any column name having string values.
Return Value
The return value of the CHAR_LENGTH() function is the length of the passed string.
Note:- To use these functions, first of all, you need to login into the MySQL server with the help of this command ( mysql -u root -h localhost -p). In this statement, root represents the name of the MySQL user, and localhost represents the name of the host. I am using localhost because MySQL server is installed in my local system.
MySQL CHAR_LENGTH() function Example
Here, I am going to see how we can use CHAR_LENGTH() function to get the length of string values. We will see various examples so that you can use this function in your daily MySQL task or in your real-life projects.
Example: Check the length of the hardcoded string
Here, I did not provide any string column name, because I want to get the length of only hardcoded strings.
mysql> select CHAR_LENGTH("Programming Funda") as "PF Length";
+-----------+
| PF Length |
+-----------+
| 17 |
+-----------+
1 row in set (0.00 sec)
Example: Check the length of a column
I have a table employee having some records, Now I am going to get the length of the first_name column of each employee record.
mysql> select e.first_name as "employee's first name", CHAR_LENGTH(e.first_name) as "length of first name" from employee e; +-----------------------+----------------------+ | employee's first name | length of first name | +-----------------------+----------------------+ | Vishvajit | 9 | | Vinay | 5 | | Minakshi | 8 | | John | 4 | | Akash | 5 | | Harshita | 8 | | Ayush | 5 | | Abhishek | 8 | | Vaishanvi | 9 | | Mahima | 6 | | Arushi | 6 | | Mahesh | 6 | | Ajay | 4 | | Manish | 6 | | David | 5 | | Nishant | 7 | +-----------------------+----------------------+ 16 rows in set (0.00 sec)
As you can in the above example, how I have used CHAR_LENGTH() function to get the length of column first_name.
MySQL CHAR_LENGTH() Function with Where Clause
In the above example, We have seen how we can get the length of any column having string values but sometimes our requirement is different and sometimes we have to do perform some actions based on the length value.
Now, I am going to list only those records whose length of the first name is greater than five.
Example
mysql> SELECT
-> e.first_name AS 'Employee\'s first name',
-> CHAR_LENGTH(e.first_name) AS 'Length of first name'
-> FROM
-> employee e
-> WHERE
-> CHAR_LENGTH(e.first_name) > 5;
+-----------------------+----------------------+
| Employee's first name | Length of first name |
+-----------------------+----------------------+
| Vishvajit | 9 |
| Minakshi | 8 |
| Harshita | 8 |
| Abhishek | 8 |
| Vaishanvi | 9 |
| Mahima | 6 |
| Arushi | 6 |
| Mahesh | 6 |
| Manish | 6 |
| Nishant | 7 |
+-----------------------+----------------------+
10 rows in set (0.00 sec)
You can see the below screenshot also where I have written this code in MySQL Workbench.
MySQL CHAR_LENGTH() With Variables
You can use CHAR_LENGTH() function to get the length of any string variables having string values.
Note:- User defined variables in MySQL are create using SET keyword.
mysql> SET @name = "Programming Funda is one the best place to learn coding"; Query OK, 0 rows affected (0.00 sec) mysql> select CHAR_LENGTH(@name) as "length of string"; +------------------+ | length of string | +------------------+ | 55 | +------------------+ 1 row in set (0.00 sec)
👉 MySQL String CHAR_LENGTH() Function Reference:- Click Here
MySQL CHARACTER_LENGTH() Function
CHARACTER_LENGTH() function is a synonym of the CHAR_LENGTH() function. We can same the CHARACTER_LENGTH() function same as CHAR_LENGTH() function. It also takes strings as parameters and returns their length.
Example
SELECT CHARACTER_LENGTH(string) FORM Source;
Parameter
It accepts a string value as a parameter.
Return Value
It returns the length of a given string.
MySQL CHARACTER_LENGTH() Function Examples
Example: getting the length of the variable
In this example, I am going to get the length of the variable that holds the string value.
mysql> SET @name = "Programming Funda is one the best place to learn coding"; Query OK, 0 rows affected (0.00 sec) mysql> select CHARACTER_LENGTH(@name) as "length of string"; +------------------+ | length of string | +------------------+ | 55 | +------------------+ 1 row in set (0.00 sec)
Example: CHARACTER_LENGTH() function with WHERE Clause
CHARACTER_LENGTH function can also be used with a WHERE clause like CHAR_LENGTH. I am about to use CHARACTER_LENGTH to list only those records whose length of the first name is greater than 5 and whose age is less than 30.
SELECT
e.first_name AS 'employee\'s first name',
CHARACTER_LENGTH(e.first_name) AS 'length of first name'
FROM
employee e
WHERE
CHARACTER_LENGTH(e.first_name) > 5
AND e.age < 30;
When you will execute the following query, you will get the following output.
Output
+-----------------------+----------------------+ | employee's first name | length of first name | +-----------------------+----------------------+ | Vishvajit | 9 | | Minakshi | 8 | | Harshita | 8 | | Vaishanvi | 9 | | Mahima | 6 | | Arushi | 6 | | Mahesh | 6 | +-----------------------+----------------------+
You can see the same query in the below screenshot which I have written in MySQL workbench.
👉 MySQL String CHARACTER_LENGTH() Function Reference:- Click Here
Summary
So, have you seen it? How we have seen a total of two ways to How to check the length of string in MySQL along with different-different examples. CHARACTER_LENGTH and CHAR_LENGTH both are synonyms of each other, you can pick any one of them at your convenience.
From now, whenever your requirement is to perform some operations based on any column length value, Then please go with these functions.
If this article is helpful for you, please visit for further interesting MySQL tutorials.
Thanks for your valuable time…. 🙏🙏❤️❤️
