Purpose of the Oracle LENGTH Function
The purpose of the LENGTH function in Oracle is to find the length of, or number of characters in, a string value.
Most programming languages and even other SQL variants have a function that does this. Let’s see how Oracle uses the LENGTH function.
The syntax of the Oracle LENGTH function is:
LENGTH ( string_value )
It returns a numeric value that represents the length of the supplied string.
The parameters of the LENGTH function are:
- string_value (mandatory): This is the string value to check the length of.
Some points to remember about the Oracle LENGTH function:
- If string_value is NULL, then LENGTH will return NULL.
- If string_value is an empty string, the LENGTH will return NULL.
- The string_value can be any of the character data types – CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB.
- If the string_value is a CHAR data type, then the LENGTH will include any trailing spaces in the value.
Examples of the LENGTH Function
Here are some examples of the Oracle LENGTH function. I find that examples are the best way for me to learn about code, even with the explanation above.
This example shows the LENGTH function using a string value.
SELECT LENGTH('Software Developer') AS LENGTH_TEST FROM dual;
The length of the string “Software Developer” is shown as 18 characters.
This example shows the length of a string value. However, we’re using a CHAR value as it has spaces at the end.
SELECT LENGTH('Software Developer ') AS LENGTH_TEST FROM dual;
The length of this string is slightly longer than the last example due to the spaces.
This example uses LENGTH with a NULL value. We’ve run the query on the table to see different results.
SELECT country, LENGTH(country) AS COUNTRY_LENGTH FROM customers;
You can see that the length of each country value is shown. Where the country is NULL, a NULL value is returned by LENGTH.
This example shows what happens when we run LENGTH on an empty string or a string of spaces.
SELECT LENGTH('') as LENGTH_EMPTY, LENGTH(' ') as LENGTH_SPACE FROM DUAL;
As you can see, the length of an empty string is NULL, but the length of a single space is 1.
This example uses LENGTH on a numeric value.
SELECT LENGTH(10973) AS LENGTH_CHECK FROM DUAL;
The LENGTH function still works on numeric values.
Some functions which are similar to the Oracle LENGTH SQL function are:
- LENGTHB – same as LENGTH but uses bytes instead of characters.
- LENGTHC – same as LENGTH but uses Unicode complete characters.
- LENGTH2 – same as LENGTH but uses UCS2 code points.
- LENGTH4 – same as LENGTH but uses UCS4 code points.
You can find a full list of Oracle functions here.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
Image courtesy of digitalart / FreeDigitalPhotos.net
Want To Improve Your Oracle SQL?
Improve your Oracle SQL queries by signing up to my free 10-day Oracle Tips email course.
BONUS: Oracle SQL functions PDF cheat sheet