SQL LENGTH Function Tutorial

SQL LENGTH FUNCTION

This is a detailed tutorial of the LENGTH Function in SQL. Learn to find the number of characters of the column values using the LENGTH function.

SQL LENGTH Function

The LENGTH Function in SQL is used to find out the number of characters of a given string. This way, you can easily find out the number of characters that forms the string of particular column values of your database table records. In some of the database systems, the LENGTH function is used as the LEN function.

Syntax

The basic syntax of the LENGTH function is written below.

MySQL.

SQL Server.

You might need to make use of the statements like SELECT along with this function for the valid working.

LENGTH Function Argument & Return Value

This function only takes one argument and that is the string in which you want to find out the number of characters. The return value is the number of characters present in the argument string as an integer number. In case, the argument provided to the LENGTH() Function is NULL, it returns NULL as well. But in case, if the argument is rather an Empty String, this function will return the length of the string as 0.

Note. The LENGTH function, instead of providing the number of characters, it returns the number of bytes in the string in some fo the relational database systems like MySQL and PostgreSQL. Both of these database systems provide another function CHAR_LENGTH to calculate the number of characters in a particular string.

But the thing that you will observe in most of the cases is that even in MySQL, both the functions CHAR_LENGTH and LENGTH are returning actually the same value. This is because, for all of the ASCII based strings, the number of bytes of a string is equal to the number of characters of the string. This may vary for character sets other than ASCII.

Examples

Have a look at the following examples to better understand the SQL LENGTH Function.

Basic Usage

The first example given below illustrates the very basic usage.

The resultset of the above query is shown in the screenshot given below.

SQL LENGTH Function Example

As the number of characters of the string provided in the LENGTH function argument is 15 including spaces, hence it returns so as an integer value.

LENGTH Vs CHAR_LENGTH For ASCII and Non-ASCII Strings

As mentioned earlier in this article that if the string contains only ASCII Characters the Function LENGTH and CHAR_LENGTH will return the same value in some relational database systems like MySQL and PostgreSQL. But for Non-ASCII Characters based string may result in different values for both of this function. This has been illustrated in the following example query.

Observe the output carefully.

SQL LENGTH Vs CHAR LENGTH Example

You can sport the difference clearly. In the first two columns, the string is the same and contains only ASCII Characters and hence both the functions return the same integer value as 13. In the other two columns, the strings are although the same but do not contain ASCII Characters and hence both of these function returns different values as in these strings the number of characters does not matches up with the number of bytes of the string.

Practical Usage

Consider the database table employees with the following structure.

Employees Demo Database Table Structure

Now, we’ll run different queries on this database table making use of the LENGTH Function for different purposes.

Query 1. Finding the length of the first_name for each of the employees.

SQL LENGTH FUNCTION Example Query

The column named LENGTH_of_first_name now displays the length of the column first_name for each of the employees.

Query 2. Finding the length of the first_name and last_name merged together.

Here we’re first concatenating the first_name and last_name column, separated by a space using the SQL CONCAT Function and then we’re applying the length function on the concatenated string-based column values and hence we’re getting the following resultset.

SQL LENGTH Function Practical Usage Example

Query 3. Finding the employees who have the largest and the shortest full names.

Here we’ll just apply the SQL MAX and MIN Function to find out the employees with the largest and the shortest full names.

The query is quite long but very easy to understand. Spend some time understanding how things are working here.

SQL LENGTH Function Practical Application Example

So the resultset contains four records of four different employees. The employee with id 11472 has the longest full name containing 27 characters while there are three different employees having the shortest names of the length of just 8 characters.

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Also, don’t forget to Subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *