SQL LENGTH/LEN Function Guide, FAQ, and Examples

In this article, we’ll look at the LENGTH function and its variants, and the VSIZE function, explain how they work, and see some examples.

Purpose of the SQL LENGTH or LEN Function

The SQL LENGTH or LEN function will find the length of, or the number of characters in, a string value.

You specify the string you want to find the length of, and the LENGTH or LEN function returns a number representing the number of characters of that string.

The function is called LENGTH in Oracle, MySQL, and Postgres, and it’s called LEN in SQL Server.

Oracle has several variants of the LENGTH function, which are the same as the basic LENGTH function – to find the length of a specified string.

However, the difference between these functions is that they are useful when working with special character sets.

The differences are:

• LENGTH: Returns the length of the string
• LENGTH2: Returns the length of the string in UCS2 code points
• LENGTH4: Returns the length of the string in UCS4 code points
• LENGTHB: Returns the length of the string in bytes
• LENGTHC: Returns the length of the string in characters

 

Purpose of the VSIZE Function

The VSIZE function returns the number of bytes of an expression using the internal representation.

That sounds a lot like the LENGTH function, doesn’t it?

I’ll explain the differences shortly after we cover the syntax and parameters.

 

SQL LENGTH/LEN Function Syntax and Parameters

The syntax of the SQL LENGTH function is:

LENGTH ( string_value )

The SQL LEN function is the same:

LEN ( string_value )

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

The syntax of the LENGTH2, LENGTH4, LENGTHB, and LENGTHC functions are all the same:

LENGTH2 ( string )
LENGTH4 ( string )
LENGTHB ( string )
LENGTHC ( string )

The parameters of the LENGTH function and its variants are:

• string_value (mandatory): This is the string value to check the length of.

Some points to remember about the SQL 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.

 

Oracle VSIZE Function Syntax and Parameters

The syntax of the VSIZE function is:

VSIZE ( expression )

The parameters of the VSIZE function are:

• expression (mandatory): The expression to calculate the VSIZE for.

If the expression value is NULL, then the function returns NULL.

 

What’s the Difference Between LENGTH and VSIZE Functions in Oracle?

If you think that the VSIZE function looks a lot like the LENGTH function, you’re right. They are pretty similar.

But, they have some differences.

• LENGTH returns the number of characters in the specified string, but VSIZE finds the number of bytes that a string uses. One byte does not always equal one character (related: character sets in Oracle).
• LENGTH takes any character argument, but VSIZE takes almost any data type, so LENGTH will have to do an implicit conversion if another data type is specified (such as a number).

So looking at VSIZE vs LENGTH in Oracle will depend on your requirements.

The examples section below looks at an example of both functions.

 

Examples of the LENGTH Functions

Here are some examples of the SQL LENGTH or SQL LEN function.

 

Example 1

This example shows the LENGTH function using a string value.

SELECT LENGTH('Software Developer');

Or, using LEN:

SELECT LEN('Software Developer');

Result:

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     ');

Result:

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;

Or, using the LEN function:

SELECT
country,
LEN(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’s 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;

Or, using LEN:

SELECT
LEN('') as LENGTH_EMPTY,
LEN(' ') 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);

Or, using LEN:

SELECT LEN(10973);

Result:

5

The LENGTH and LEN function still works on numeric values.

 

Example 6 – LENGTH Function Variants

This example shows how each of the LENGTH functions treats a specific string.

SELECT
'Database Star table column row' AS sample,
LENGTHB('Database Star table column row') AS lengthb_test,
LENGTHC('Database Star table column row') AS lengthc_test,
LENGTH2('Database Star table column row') AS length2_test,
LENGTH4('Database Star table column row') AS length4_test
FROM dual;

Result:

SAMPLE	LENGTHB_TEST	LENGTHC_TEST	LENGTH2_TEST	LENGTH4_TEST
Database Star table column row	30	30	30	30

 

Examples of the VSIZE Function

Here are some examples of the VSIZE function.

Example 1 – Basic VSIZE

This example is a basic example of the VSIZE function.

SELECT
VSIZE('DatabaseStar')
FROM dual;

Result:

12

The result is 12 because 12 bytes would be used.

 

Example 2 – VSIZE vs LENGTH

Let’s see how VSIZE and LENGTH compare using an example.

First, let’s set up a new table with some data.

CREATE TABLE vsize_test (
  single_byte_char VARCHAR2(10),
  multi_byte_char NVARCHAR2(10)
);

INSERT INTO vsize_test (single_byte_char, multi_byte_char)
VALUES ('Database', 'Databӑse');

Now, let’s select these values.

SELECT single_byte_char, multi_byte_char
FROM vsize_test;

Result:

SINGLE_BYTE_CHAR	MULTI_BYTE_CHAR
Database	Datab?se

Now, let’s use the VSIZE and LENGTH functions on these values.

SELECT
single_byte_char,
VSIZE(single_byte_char) AS vsize_single,
LENGTH(single_byte_char) AS length_single,
multi_byte_char,
VSIZE(multi_byte_char) AS vsize_multi,
LENGTH(multi_byte_char) AS length_multi
FROM vsize_test;

Result:

SINGLE_BYTE_ CHAR	VSIZE_ SINGLE	LENGTH_ SINGLE	MULTI_BYTE_ CHAR	VSIZE_ MULTI	LENGTH_ MULTI
Database	8	8	Datab?se	16	8

You can see that the results are different here.

 

Similar Functions

Some functions which are similar to these functions are:

• VSIZE: Finds the size of a string, similar to Length.
• INSTR2/INSTR4/INSTRB/INSTRC: The variations of the INSTR function which find a smaller string inside a larger string.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.