Oracle VSIZE Function with ExamplesIn this article, I’ll explain what the Oracle VSIZE function does and show you some examples.

Purpose of the Oracle 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.



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



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;



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

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



You can see that the results are different here.

Similar Functions

Some functions which are similar to the VSIZE function are:

  • LENGTH: Returns the number of characters in the specified string.

If you want to know more about SQL functions, you can find a full list of Oracle SQL 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!

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

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit