FB

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 Oracle LENGTH Function

The Oracle LENGTH 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 function returns a number representing the number of characters of that string.Oracle LENGTH Functions

Most programming languages and even other SQL variants have a function that does this. Let’s see how Oracle uses the LENGTH function.

The purpose of these LENGTH function variants is the same as the basic LENGTH function – to find the length of a specified string.

However, the difference with 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

Get Your Free PDF: 9 Ways to Improve your Database Skills

 

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.

 

Oracle LENGTH Function Syntax and Parameters

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 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 Oracle LENGTH function and its variants:

  • 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 Oracle LENGTH function and its variants. 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.

 

Example 6 – LENGTH Function Variants

This example shows how each of the LENGTH functions treat 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') AS vsize_test
FROM dual;

Result:

VSIZE_TEST
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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Get Your SQL Function Cheat Sheet Now: