Oracle LENGTH()

Introduction to Oracle LENGTH()

Oracle LENGTH( ) function can be defined as a function which is used to calculate the length of a string and it measures the length of the string in characters (A single string is made of many characters and the data type of the string expression can be VARCHAR2, CHAR, NCHAR, CLOB or NCLOB) as defined by the input character set and it returns a positive value upon execution which is a integer representing the number of characters present in the string.

Syntax

Let us now discuss about the syntax of the LENGTH( ) function. The syntax is very simple.

LENGTH(string_expression)

Parameter

string_expression: This refers to the string whose length we are going to find. The string_expression parameter can be a variable, constant or a column of a table.

How LENGTH Function Works In Oracle

The length function has discussed earlier is used to return the length of the string which we pass as a parameter. It accepts only one argument or parameter. The function when executed calculates the total number of characters present in the expression and then returns a positive integer which equals the number of characters. The point to keep in mind that in case the string_expression is NULL then the length function returns NULL. Another important case is that if the string_expression is of CHAR datatype then it will calculate both trailing as well as leading blanks.

Examples to Implement LENGTH( ) Function in Oracle

Let us look at the examples for ORACLE LENGTH( ) function for getting a better understanding of different cases where we can use the function.

Example #1

LENGTH OF A STRING: In this case we will pass a string constant inside the parenthesis of the length function. The length function will be used to find the length of the string. Let us look at the query.

Code:

SELECT LENGTH('Ram is a good boy')"LENGTH OF THE STRING" from DUAL;

Output:

Explanation: In this query the string expression used is: Ram is a good boy. Let us execute the query in SQL developer and check the output of the query.As we can see in the above screen shot the output shows the length of the string.

Example #2

LENGTH FUNCTION WHERE THE STRING EXPRESSION IS NULL: In the second case we will check how the length function responds when the string expression passed inside the parenthesis of the function is NULL. Let us look at the query for the same.

Code:

SELECT LENGTH('')"LENGTH OF THE STRING" from DUAL;

Output:

Explanation: As we can see in the query that the expression passed is NULL. Let us now execute the same query in SQL DEVELOPER. As we can see in the above screen shot that upon execution the query returns the length as NULL.

Example #3

LENGTH FUNCTION ON COLUMN OF A TABLE: As we had discussed earlier in this article that the length function can also be applied to columns of a table.

Consider Following Employee Table:

select * from Employee;

In this example we will try to get the length of each employee PROFILE present in the PROFILE column of the EMPLOYEE table in oracle database and we will order by descending order. Let us look at the query for the same.

Code:

SELECT
PROFILE,
LENGTH(PROFILE) "LENGTH OF THE PROFILE"
FROM
Employee
ORDER BY
LENGTH(PROFILE) DESC;

Output:

Explanation: As we can see in the above query we have used the column PROFILE to find the length of each employee PROFILE present in the column PROFILE.As we can see that the output consists of a separate column displaying the length of every employee name present in the name column.

Example #4

LENGTH FUNCTION USING GROUP BY CLAUSE: In this case we will discuss the use of length function using GROUP BY clause. In this example we will group the employees present in the employee table by the length of their PROFILE and also displays the count of employee PROFILE we have in each group. Let us now look at the query for the same.

Code:

SELECT
LENGTH(PROFILE) "LENGTH OF THE PROFILE",
COUNT(*)
FROM
Employee
GROUP BY
LENGTH(PROFILE);

Output:

Explanation: As we can see in the above query that we have used length function with the group by clause and count function to count the number of employees present in each group. As we can see in the output screen shot that the query groups the employee table by the length of the employee PROFILE and also displays the count of employee PROFILE in each group.

Example #5

LENGTH FUNCTION WITH CASE: We will now check the use of length function on condition statements like case and along with it we will also use concat function. In this example we will only add three dots with the PROFILE whose length are more than FIVE characters and rest PROFILES will be displayed as usual. To achieve this we will use CASE statement for the condition and we will concat function to add the three dots to only those PROFILE whose length will be more than five characters. Let us now look at the query for the same.

Code:

SELECT
PROFILE,
CASE
WHEN LENGTH( PROFILE ) > 5 THEN CONCAT( PROFILE, '...' )
ELSE PROFILE
END NEW_PROFILE
FROM
Employee
ORDER BY
PROFILE desc;

Output:

Explanation: As we can see in the query that the alias NEW_PROFILE represents the CASE block and the PROFILE column represents the actual original PROFILES of the employees. As we can see in the screen shot that two names have three dots attached to them as they satisfy the condition.

Conclusion

In this article we discussed about the definition of the length function in oracle along with the syntax. Later in the article we discussed about the various examples representing various condition on which the length function can be used.

Recommended Articles

This is a guide to Oracle LENGTH(). Here we discuss an introduction to Oracle LENGTH(), syntax, how does it work, examples with code and output. You can also go through our other related articles to learn more –

1. Oracle Subquery
2. Function in Oracle
3. Natural Join in Oracle
4. Oracle Window Functions
5. Oracle While Loop | How to Works?