PL/SQL Strings

We will learn 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 optimal size parameter. Strings are sequences of characters, and PL/SQL provides a rich set of functions and operators to work with them.

Strings in PL/SQL

The string in PL/SQL is a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters, or a combination of all. PL/SQL offers three kinds of strings −

• Fixed Length Strings: When we define a fixed length string we must specify the size of the string while declaration. Once we declare a fixed-length string the variable occupies the memory space equal to the length of the string filling the memory space with empty spaces.
  
• Variable Length Strings: In such strings, when a variable-length string is first declared, it is given no space. In the case of a variable-length string, it can be up to 32,767 characters long.
  
• Character Large Objects: Character Large Objects, commonly known as CLOBs, are data types in database systems, including PL/SQL (used in Oracle Database), designed to store large amounts of character data. CLOBs are particularly useful when dealing with text data that exceeds the limitations of regular character data types.

PL/SQL strings could be either variables or literals. A string literal is enclosed within quotation marks. For example,

' Welcome to GeeksForGeeks' 

To include a single quote inside a string literal, you need to type two single quotes next to one another. For example,

'GeeksForGeeks' is a best platform for learning , isn''t ?'

In MySQL, there are several data types that you can use to represent strings. Here are some commonly used string data types

1. CHAR(n)

• Fixed-length character string.
• Requires a specified length 'n'.
• Trailing spaces are padded with spaces.

DECLARE var CHAR(n);

2. VARCHAR(n)

• Variable-length character string.
• Requires a specified maximum length 'n'.
• Only consumes as much storage as needed for the actual data.

DECLARE var VARCHAR(255);

3. TEXT

• Variable-length character string with a very large maximum length (65,535 characters).
• Suitable for large amounts of text data.

DECLARE my_text_variable TEXT;

4. BINARY(n)

• Fixed-length binary string (binary data).
• Requires a specified length 'n'.

DECLARE binary_var BINARY(16);

5. VARBINARY(n)

• Variable-length binary string (binary data).
• Requires a specified maximum length 'n'.
• Only consumes as much storage as needed for the actual data.

DECLARE binary_var VARBINARY(255)

6. BLOB

• Variable-length binary string with a very large maximum length (65,535 bytes).
• Suitable for large binary objects.

DECLARE blob_var BLOB;

MySQL String Functions and Operators

Exmaples of PL/SQL Strings

In this article, we’re going to look at a few examples to help you get a better idea of these topics

Example 1: How to Concatenate Two Strings

DECLARE
   first_name VARCHAR2(20) := 'John';
   last_name VARCHAR2(20) := 'Doe';
   full_name VARCHAR2(50);
BEGIN
   -- Concatenate first and last names
   full_name := first_name || ' ' || last_name;
   DBMS_OUTPUT.PUT_LINE('Full Name: ' || full_name);
END;
/

When the above code is executed in SQL prompt , it produces following result:

Full Name: John Doe

Example 2: SUBSTR() Function

DECLARE
   original_string VARCHAR2(50) := 'Hello, World!';
   substring_result VARCHAR2(20);
BEGIN
   -- Extract substring starting at position 7 with a length of 5
   substring_result := SUBSTR(original_string, 7, 5);
   DBMS_OUTPUT.PUT_LINE('Substring: ' || substring_result);
END;
/

When the above code is executed , following output is produced:

Substring: World

Example 3: How to Convert a String to Lowercase and Uppercase

DECLARE
   input_string VARCHAR2(20) := 'Hello, PL/SQL!';
   uppercase_result VARCHAR2(20);
   lowercase_result VARCHAR2(20);
BEGIN
   -- Convert to uppercase
   uppercase_result := UPPER(input_string);
   DBMS_OUTPUT.PUT_LINE('Uppercase: ' || uppercase_result);

   -- Convert to lowercase
   lowercase_result := LOWER(input_string);
   DBMS_OUTPUT.PUT_LINE('Lowercase: ' || lowercase_result);
END;
/

When the above code is executed , it produces following output:

Uppercase: HELLO, PL/SQL!
Lowercase: hello, pl/sql!

Example 4: Pattern Matching

DECLARE
   email VARCHAR2(50) := '[email protected]';
BEGIN
   -- Check if the email starts with 'john'
   IF email LIKE 'john%' THEN
      DBMS_OUTPUT.PUT_LINE('Email starts with ''john''.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Email does not start with ''john''.');
   END IF;
END;
/

When the above code is executed , it produces following result:

Email starts with 'john'.

Conclusion

We discussed types of strings in PL/SQL. We discussed all the string functions and operators with proper description. We also discussed the syntax for declaring a string and using it in a PL/SQL code block. PL/SQL string handling allows developers to build efficient, secure, and flexible applications that leverage the full potential of Oracle databases.