Back to: MySQL Tutorials for Beginners and Professionals
MySQL LENGTH and CHAR_LENGTH Function with Examples
In this article, I am going to discuss MySQL LENGTH and CHAR_LENGTH Function with Examples. Please read our previous article where we discussed MySQL MID Function with Examples.
MySQL LENGTH Function:
The LENGTH function returns the length of a given string measured in bytes. The LENGTH function only works on string columns. The MySQL LENGTH function is similar to the LEN function of SQL Server.
LENGTH (str)
Parameter
- str: Required. The string value whose length is to be returned.
Examples: MySQL LENGTH Function
Let us see how to use the MySQL LENGTH function with some examples
Example: Passing NULL to the LENGTH function and it will return NULL as output
SELECT LENGTH(NULL);
Output: NULL
Example: Passing an empty string to the LENGTH function and it will return 0 as the output
SELECT LENGTH(”);
Output: 0
Example: Passing a single space to the LENGTH function and will return 1 as the output.
SELECT LENGTH(‘ ‘);
Output: 1
Example: Passing string with space to the LENGTH function and it will return the number of characters including space present in the string as output.
SELECT LENGTH(‘DOT NET TUTORIALS’);
Output: 17
Example: Passing string without space to the LENGTH function and will return the number of characters present in the string as the output.
SELECT LENGTH(‘DOTNETTUTORIALS’);
Output: 15
MySQL CHAR_LENGTH Function:
The CHAR_LENGTH function returns the length of a given string measured in character. The CHAR_LENGTH function only works on string columns.
CHAR_LENGTH( str ))
Parameter
- str: Required. The string value whose length is to be returned.
Examples: MySQL CHAR_LENGTH Function
Let us see how to use the MySQL CHAR_LENGTH function with some examples
Example: Passing NULL to the CHAR_LENGTH function and it will return NULL as output
SELECT CHAR_LENGTH(NULL);
Output: NULL
Example: Passing an empty string to the CHAR_LENGTH function and it will return 0 as the output
SELECT CHAR_LENGTH(”);
Output: 0
Example: Passing a single space to the CHAR_LENGTH function and will return 1 as the output.
SELECT CHAR_LENGTH(‘ ‘);
Output: 1
Example: Passing string with space to the CHAR_LENGTH function and it will return the number of characters including space present in the string as output.
SELECT CHAR_LENGTH(‘DOT NET TUTORIALS’);
Output: 17
Example: Passing string without space to the CHAR_LENGTH function and it will return the number of characters present in the string as the output.
SELECT CHAR_LENGTH(‘DOTNETTUTORIALS’);
Output: 15
Working with Database Table:
The LENGTH and CHAR_LENGTH functions only work on string columns. Following is the syntax to use the MySQL LENGTH and CHAR_LENGTH function,
SELECT LENGTH(columnname) FROM tablename;
SELECT CHAR_LENGTH(columnname) FROM tablename;
Understanding LENGTH and CHAR_LENGTH Functions with Database Table:
We are going to use the following Employee table to understand the MySQL LENGTH and CHAR_LENGTH functions with Examples.
Please use the following SQL Script to create and populate the Employee table with the required sample data.
CREATE DATABASE Company; USE Company; CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(45) NOT NULL, Department VARCHAR(45) NOT NULL, Salary FLOAT NOT NULL, Gender VARCHAR(45) NOT NULL, Age INT NOT NULL, City VARCHAR(45) NOT NULL ); INSERT INTO Employee VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London'); INSERT INTO Employee VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London'); INSERT INTO Employee VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London'); INSERT INTO Employee VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London'); INSERT INTO Employee VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai'); INSERT INTO Employee VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai'); INSERT INTO Employee VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai'); INSERT INTO Employee VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
Example:
Now, we need to calculate the length of the Employee name using LENGTH and CHAR_LENGTH functions by executing the below SQL Statement.
SELECT Name, LENGTH(Name) AS ‘Length’, CHAR_LENGTH(Name) AS ‘Char_Length’ FROM Employee;
Once you execute the above SQL Script, you will get the following output.
Difference Between LENGTH() and CHAR_LENGTH() in MySQL
It is important to understand the difference between the LENGTH() and CHAR_LENGTH() functions. The result of both these functions may appear to be the same even if it’s not. The MySQL CHAR_LENGTH() function returns the number of characters in its argument whereas the MySQL LENGTH() function returns the length of a string in bytes (not the number of characters).
The LENGTH function returns the length of the string in bytes. So, it will not suitable for UNICODE character strings since they are multibyte. In that case, you need to use the CHAR_LENGTH function in MySQL to just count the number of characters irrespective of single-byte or multibyte characters.
In the next article, I am going to discuss MySQL ROUND Function with Examples. Here, in this article, I try to explain the MySQL LENGTH and CHAR_LENGTH Function with Examples. I hope this MySQL LENGTH and CHAR_LENGTH Function article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.
