In this tutorial, we will learn how to use the length() function in Oracle databases to determine the length of a given string.
Strings are an essential data type for databases and development in general. Therefore, understanding the various techniques to manipulate and interact with strings is immensely beneficial.
Oracle Length Function
Oracle provides the length() function, which allows us to input a given string. The function will then determine the length of the string and return it as an integer value.
The string length is determined by the number of characters in the input string and governed by the defined character set.
We can express the function syntax as shown below:
The previous syntax shows that the function accepts one argument: a string value, variable, table column, or string expression.
The input type must be CHAR, VARCHAR2, NCHAR, NVARCHAR, CLOB, or NCLOB.
If the provided input_string is a char type, the function will include any/all leading and trailing whitespace characters as part of the string.
The function will return a positive integer type denoting the number of characters of the string. The function will produce a NULL type if the input value is NULL.
Example Function Usage
Let us use a practical example to understand best how the length() function works.
Example 1 – Basic Function Example
The following example uses the length() function with a literal input string:
from dual;
The previous query should return the number of characters of the input string as illustrated below:
------------
20
Example 2 – Using the Length Function With NULL Input
The following example shows the function operation when provided with a NULL input:
Result:
----------
<null>
It is good to understand that an empty string is treated in a similar format as NULL. Demonstration:
Output:
-------------
<null>
Example 3 – String With Leading and Trailing Whitespace Characters
The following example reveals how the function behaves when provided with a literal string with leading or trailing whitespace characters:
length('hello world') as len_trailing,
length('hello world') as len_leading_trailing,
length('hello world') as len_none
from dual;
The previous statement should return the output as shown below:
Example 4 – Using the Length() Function With Table Column
The following example demonstrates the usage of the length function with a table column:
from EMPLOYEES
where ROWNUM <= 10;
The previous code should return the length of the input string in the first_name column. An example output is shown below:
Conclusion
In this post, you discovered the syntax and return value of the length function in Oracle. You also came across various examples of usage of the length function.
