Menu Close

How to check the Length of String in MySQL

how to check length of String in MySQL

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.

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.

How to check the length of string in MySQL

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.

How to check the length of string in MySQL
πŸ‘‰ 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…. πŸ™πŸ™β€οΈβ€οΈ

How to Convert Excel to JSON in Python
Online MySQL Tutorial - 2023

Related Posts