Updated April 1, 2023
Introduction to PLSQL length
PL/SQL provides the different types of functions to the user, the length () is one of the functions that the PL/SQL provides. Basically, the length () function is used to return the length of the string as per requirement. In other words, we can say that it is used to return the length of a character. The PL/SQL length () function accepts any data type such as NCHAR, CHAR, VARCHAR2, CLOB, etc. After execution of the length () function, it returns the length of the string, and the data type of that value is the number. The length includes all trailing blanks if the data type of the char input is CHAR. If we provide a null to length function, then the length () function returns the null value. In this topic, we are going to learn about PLSQL length.
Syntax
LENGTH(specified string expression )Explanation
In the above expression, we used the length () function as shown; inside the function, we pass a specified string whose length we need to find, and the data type of string can be VARCHAR2, NCHAR, CLOB, etc. that is dependent on the user.
How does length work in PL/SQL?
Now let’s see how length () function id works in PL/SQL as follows.
A string, also known as character data, is a collection of symbols chosen from a set of characters. To put it another way, the symbols in a string may be English letters like “A” or “B.”
In PL/SQL, there are three types of strings:
Strings with a set length: The declaration specifies that the text is right-padded with spaces to the length given in the declaration.
Strings with varying lengths: The string’s maximum length (which must not exceed 32,767) is given, but no padding is applied.
Large items with a distinctive personality (CLOBs): CLOBs are long strings that may be up to 128 terabytes in length.
Now let’s see how we can declare a string in PL/SQL as follows.
Declare variables to hold string values in your PL/SQL applications when working with strings. To declare a string variable, choose from Oracle Database’s various string data types, which include CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB.
The data types with an ”N” prefix are “national character set” data types, which implies they hold Unicode character data. (Unicode is a universally encoded character set that may be used to store information in any language.)
You must provide the maximum length of a variable-length string when declaring it. For example, the following code declares a variable with the VARCHAR2 data type to contain a country name that cannot exceed 100 characters.
Country_name varchar(100);Basically, we can provide a specified string to the length function as per our requirement in any one of the data types such as NCLOB, CHAR, NCHAR, CLOB, VARCHAR2, or NVARCHAR2.
After execution of the length () function, return a positive integer number that represents the length of the specified string. If the specified string is null, then the length () function returns the null value, and the specified string is the char data type, then it returns the length of the string with trailing blanks.
The LENGTH functions return the length of char. LENGTH determines length based on the characters in the supplied character set. Instead of using letters, LENGTH utilizes bytes. LENGTH makes use of full Unicode characters. UCS2 code points are used in LENGTH2. LENGTH4 uses UCS4 code points. CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB are all valid data types for char, and they return value as NUMBER data type. The length of char with the data type CHAR includes all trailing blanks. This method returns null if char is null.
The length () function is supported following the version of Oracle/PL/SQL.
Oracle 8i, 8i, 10g, 11g, 12c etc.
Examples of PLSQL length
Now let’s see the different examples of length () function in PL/SQL for better understanding as follows.
First, let’s see an example of a string with null as follows.
DECLARE
sample_string string(100) := NULL;
BEGIN
dbms_output.put_line(LENGTH(sample_string)) ;
END;
/Explanation
In the above example, we declare the string variable as sample_string as shown, here string size is 100 means we can store 100 characters, but in this example, we assign string as NULL, as shown in the above example. Then, in the execution section, we write the procedure to display the length of strings by dbms_output.put_line statement; after that, we end the procedure and execute the procedure. The final output of the above procedure we illustrated by using the following screenshot as follows.
Output:
Now let’s see another example as follows.
DECLARE
sample_string string(100) := ' ';
BEGIN
dbms_output.put_line(LENGTH(sample_string)) ;
END;
/Explanation
In the above example, we follow the same procedure as the above example; in this example, we assign the two blank spaces as shown. Then, inside the execution section, we write a statement to display the length of the string; in this example length of the string is 2. The final output of the above procedure we illustrated by using the following screenshot as follows.
Output:
Now let’s see another example that contains the string as follows.
DECLARE
sample_string string(100) := 'Welcome in PLSQL';
BEGIN
dbms_output.put_line(LENGTH(sample_string)) ;
END;
/Explanation
In the above example, in the declare section, we declare a string variable containing the string “Welcome in PLSQL” as shown; after that, we print that variable using dbms_output.put_line statement. The final output of the above procedure we illustrated by using the following screenshot as follows.
Output:
Now let’s one more example that contains the string as well as blank space as follows.
DECLARE
sample_string string(100) := 'Welcome in PLSQL ';
BEGIN
dbms_output.put_line(LENGTH(sample_string)) ;
END;
/Explanation
In the above example, we add five blank spaces in the declaration section, as shown in the above example, and we just execute the procedure. Notice here the length () function counts the blank in the string. The final output of the above procedure we illustrated by using the following screenshot as follows.
Output:
Conclusion
We hope from this article you learn PL/SQL length. From the above article, we have learned the basic syntax of length, and we also see different examples of the length. From this article, we learned how and when we use PL/SQL length.
Recommended Articles
We hope that this EDUCBA information on “PLSQL length” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
