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.

 

Syntax

The syntax of the VSIZE function is:

VSIZE ( expression )

 

Parameters

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.

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.

Now, let’s select these values.

Result:

SINGLE_BYTE_CHARMULTI_BYTE_CHAR
DatabaseDatab?se

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

Result:

SINGLE_BYTE_ CHARVSIZE_ SINGLELENGTH_ SINGLEMULTI_BYTE_ CHARVSIZE_ MULTILENGTH_ MULTI
Database88Datab?se168

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 21-day Oracle Tips email course.

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