Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
We will learn about Strings in PL/SQL in this article, which will cover several types of strings, the syntax for declaring a string variable, and then utilizing it in a PL/SQL code block. In PL/SQL, a string is a sequence of characters with an optional size parameter.
In PL/SQL, a string is a collection of characters that can be numbers, special characters, alphabets, or all of them at once, just like in any other programming language. The flexibility of PL/SQL allows us to create variable-length strings, with the size of the string being optional.
There are mainly three types of strings. They are as follows:
Strings with fixed lengths
When defining a fixed-length string, the size of the string must be specified at the time of declaration. When we declare a variable with a fixed-length string, the variable fills the memory space with empty spaces equal to the length of the string.
Strings with variable length
When a variable-length string is first declared, it is given no space. In the case of a variable-length string, the string can be up to 32,767 characters long.
Character large object(CLOBs)
This is a sort of variable-length string that can hold up to 128 terabytes of data. Strings in PL/SQL can be variables or literals. Within quotation marks, a literal string is enclosed. As an example,
'Hello world' or 'This looks really good'
If we want to include a single quote into a string, we have to use two single quotes. For example:
'It''s easy to repeat.'
Declaring String Variables
When declaring a string variable, we must supply the datatype as well as the length value, which determines the string's maximum length.
CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB are just a few of the string datatypes available in Oracle. National character set data types are prefixed with an 'N,' and they store Unicode character data.
The following example will give you an overview of declaring and using some string variables-
DECLARE
name varchar2(20);
schoolname varchar2(20);
address clob;
grade char(1);
BEGIN
name := 'Joey ';
schoolname :='Delhi public school';
address := 'Street: 12,2nd Floor, 22/26, J D Trust Building, Daryasthan Street, Delhi';
grade := 'A';
IF grade = 'A' THEN
dbms_output.put_line(name);
dbms_output.put_line(schoolname);
dbms_output.put_line(address);
END IF;
END;
/
When the preceding code is run at the SQL prompt, the following is the result:
Joey
Delhi public school
Street: 12,2nd Floor, 22/26, J D Trust Building, Daryasthan Street, Delhi
PL/SQL procedure successfully completed
Use the CHAR data type to declare a fixed-length string. For a fixed-length variable, you don't need to provide a maximum length. If you don't specify a length constraint, Oracle Database will utilize the maximum length allowed. The two declarations that follow are identical:
favchar CHAR(1):= 'S';
favchar CHAR:= 'S';
PL/SQL String Functions and Operators
The string functions are given by PL/SQL are listed in the table below.
Function
Purpose
ASCII(x);
The ASCII value of the character x is returned.
CHR(x);
The character with the ASCII value of x is returned.
CONCAT(x, y);
Returns the additional string after concatenating the strings x and y.
INITCAP(x);
Returns a string that converts the first letter of each word in x to uppercase.
INSTR(x, find_string [, start] [, occurrence]);
Finds find_string in x and returns the location where it appears.
INSTRB(x);
It returns the value in bytes for the location of a string within another string.
LENGTH(x);
The number of characters in x is returned.
LENGTHB(x);
For a single-byte character set, this function returns the length of a character string in bytes.
LOWER(x);
Returns a string that converts the letters in x to lowercase.
LPAD(x, width [, pad_string]);
Pads x to the left with spaces to bring the string's total length to width characters.
LTRIM(x [, trim_string]);
Characters to the left of x are trimmed.
NANVL(x, value);
If x matches the NaN special value (not a number), value is returned; else, x is returned.
NLS_INITCAP(x);
The INITCAP function is similar to INITCAP with the exception that it can use a different sort method as defined by NLSSORT.
NLS_LOWER(x);
It's the same as the LOWER function, only it can use a different sort technique than NLSSORT specifies.
NLS_UPPER(x);
The only difference is that it can use a different sort algorithm than the UPPER function, as stated by NLSSORT.
NLSSORT(x);
Changes the way the characters are sorted. Any NLS function must be preceded by this argument; otherwise, the default sort will be used.
NVL(x, value);
If x is null, the value is returned; else, x is returned.
NVL2(x, value1, value2);
If x is not null, value1 is returned; if x is null, value2 is returned.
REPLACE(x, search_string, replace_string);
Replaces search string with replacing string after searching x for it.
RPAD(x, width [, pad_string]);
To the right, pad x.
RTRIM(x [, trim_string]);
Removes x from the right side.
SOUNDEX(x) ;
The phonetic representation of x is returned as a string.
SUBSTR(x, start [, length]);
Returns a substring of x that starts where the start is given. A substring's length can be specified as an option.
SUBSTRB(x);
For single-byte character systems, the parameters are represented in bytes rather than characters, as in SUBSTR.
TRIM([trim_char FROM) x);
Trims the characters on x's left and right sides.
UPPER(x);
Returns a string that converts the letters in x to uppercase.
What is the use of the LENGTH(x) function in PL/SQL?
LENGTH(x) function is used to return the number of characters present in x. For example, LENGTH(‘ball’) returns 4.
How can we use a single quote in a string?
If we want to include a single quote into a string, we have to use two single quotes. For example: ‘It’ ’s easy to repeat.
What type of string data types are available in Oracle?
CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB are some types of string datatypes available in Oracle.
Key Takeaways
To summarize the article, we discussed all the strings functions and operators with proper description. We discussed the syntax for declaring a string and using it in the PL/SQL code block.
Hope you learned something. But the knowledge never stops, so to better understand the Database management system, you can go through many articles on our platform.
Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job. Check out this problem - Longest String Chain