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.
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;
|SINGLE_BYTE_ CHAR||VSIZE_ SINGLE||LENGTH_ SINGLE||MULTI_BYTE_ CHAR||VSIZE_ MULTI||LENGTH_ MULTI|
You can see that the results are different here.
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!