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) := 'john.doe@example.com';
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'.

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.