Oracle LENGTHThe Oracle LENGTH function is a simple function to use. Learn all of the features of this function and some examples in this article.

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.

 

Syntax

The syntax of the Oracle LENGTH function is:

LENGTH ( string_value )

It returns a numeric value that represents the length of the supplied string.

 

Parameters

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.

Example 1

This example shows the LENGTH function using a string value.

SELECT LENGTH('Software Developer') AS LENGTH_TEST
FROM dual;

Result:

LENGTH_TEST
18

The length of the string “Software Developer” is shown as 18 characters.

 

Example 2

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;

Result:

LENGTH_TEST
23

The length of this string is slightly longer than the last example due to the spaces.

 

Example 3

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;

Result:

COUNTRY COUNTRY_LENGTH
USA 3
USA 3
Canada 6
UK 2
USA 3
(null) (null)
France 6
(null) (null)

You can see that the length of each country value is shown. Where the country is NULL, a NULL value is returned by LENGTH.

 

Example 4

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;

Result:

LENGTH_EMPTY LENGTH_SPACE
(null) 1

As you can see, the length of an empty string is NULL, but the length of a single space is 1.

 

Example 5

This example uses LENGTH on a numeric value.

SELECT LENGTH(10973) AS LENGTH_CHECK FROM DUAL;

Result:

LENGTH_CHECK
5

The LENGTH function still works on numeric values.

 

Similar Functions

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

Improve Your Oracle SQL With My 10-Day Email Course

x