PL/SQL - Strings: A Beginner's Guide

Hello, future PL/SQL wizards! Today, we're going to embark on an exciting journey into the world of strings in PL/SQL. Don't worry if you've never written a line of code before – I'll be your friendly guide, and we'll take this step by step. By the end of this tutorial, you'll be manipulating strings like a pro!

What are Strings?

Before we dive in, let's start with the basics. In programming, a string is simply a sequence of characters. It could be a word, a sentence, or even a whole paragraph. Think of it as any text you might type on your keyboard.

Declaring String Variables

In PL/SQL, we need to declare our variables before we can use them. It's like introducing yourself before you start a conversation – it's just good manners!

Basic String Declaration

Let's start with the simplest way to declare a string variable:

DECLARE
  my_name VARCHAR2(50) := 'John Doe';
BEGIN
  DBMS_OUTPUT.PUT_LINE('My name is: ' || my_name);
END;

In this example:

• We use the DECLARE keyword to start our variable declarations.
• my_name is the name of our variable.
• VARCHAR2(50) means it's a variable-length string that can hold up to 50 characters.
• := is the assignment operator, like an equals sign.
• 'John Doe' is the initial value we're assigning to my_name.

When we run this code, it will output: "My name is: John Doe"

Using Different String Types

PL/SQL offers several string types. Let's look at a few:

DECLARE
  v_varchar2 VARCHAR2(20) := 'Hello, World!';
  v_char CHAR(10) := 'Fixed     ';
  v_long LONG := 'This is a long string that can store up to 32,760 characters';
BEGIN
  DBMS_OUTPUT.PUT_LINE('VARCHAR2: ' || v_varchar2);
  DBMS_OUTPUT.PUT_LINE('CHAR: ' || v_char);
  DBMS_OUTPUT.PUT_LINE('LONG: ' || v_long);
END;

Here's what's happening:

• VARCHAR2 is the most common type, storing variable-length strings.
• CHAR is for fixed-length strings. Notice how it pads the extra space with blanks.
• LONG can store very large strings, but it's less flexible in some operations.

PL/SQL String Functions and Operators

Now that we know how to declare strings, let's have some fun with them!

Concatenation

Concatenation is just a fancy word for "joining strings together". In PL/SQL, we use the || operator:

DECLARE
  first_name VARCHAR2(20) := 'Jane';
  last_name VARCHAR2(20) := 'Smith';
  full_name VARCHAR2(41);
BEGIN
  full_name := first_name || ' ' || last_name;
  DBMS_OUTPUT.PUT_LINE('Full name: ' || full_name);
END;

This will output: "Full name: Jane Smith"

String Functions

PL/SQL comes with a treasure trove of built-in functions to manipulate strings. Let's explore some of the most useful ones:

Function	Description	Example
UPPER	Converts string to uppercase	UPPER('hello') returns 'HELLO'
LOWER	Converts string to lowercase	LOWER('WORLD') returns 'world'
INITCAP	Capitalizes first letter of each word	INITCAP('john doe') returns 'John Doe'
LENGTH	Returns the length of the string	LENGTH('OpenAI') returns 6
SUBSTR	Extracts a portion of the string	SUBSTR('HelloWorld', 1, 5) returns 'Hello'
TRIM	Removes specified characters from start/end	TRIM(' Hello ') returns 'Hello'
REPLACE	Replaces all occurrences of a substring	REPLACE('JACK and JILL','J','BL') returns 'BLACK and BLILL'

Let's see these in action:

DECLARE
  v_string VARCHAR2(50) := '  hello, world!  ';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Original: ' || v_string);
  DBMS_OUTPUT.PUT_LINE('UPPER: ' || UPPER(v_string));
  DBMS_OUTPUT.PUT_LINE('LOWER: ' || LOWER(v_string));
  DBMS_OUTPUT.PUT_LINE('INITCAP: ' || INITCAP(v_string));
  DBMS_OUTPUT.PUT_LINE('LENGTH: ' || LENGTH(v_string));
  DBMS_OUTPUT.PUT_LINE('SUBSTR: ' || SUBSTR(v_string, 3, 5));
  DBMS_OUTPUT.PUT_LINE('TRIM: ' || TRIM(v_string));
  DBMS_OUTPUT.PUT_LINE('REPLACE: ' || REPLACE(v_string, 'o', '0'));
END;

This code will output:

Original:   hello, world!  
UPPER:   HELLO, WORLD!  
LOWER:   hello, world!  
INITCAP:   Hello, World!  
LENGTH: 16
SUBSTR: hello
TRIM: hello, world!
REPLACE:   hell0, w0rld!  

Putting It All Together

Now, let's combine what we've learned into a more complex example:

DECLARE
  v_sentence VARCHAR2(100) := 'The quick brown fox jumps over the lazy dog';
  v_word VARCHAR2(20);
  v_count NUMBER;
BEGIN
  -- Count the number of words
  v_count := LENGTH(v_sentence) - LENGTH(REPLACE(v_sentence, ' ', '')) + 1;
  DBMS_OUTPUT.PUT_LINE('Number of words: ' || v_count);

  -- Extract and capitalize the 4th word
  v_word := INITCAP(SUBSTR(v_sentence, 
                    INSTR(v_sentence, ' ', 1, 3) + 1,
                    INSTR(v_sentence, ' ', 1, 4) - INSTR(v_sentence, ' ', 1, 3) - 1));
  DBMS_OUTPUT.PUT_LINE('The 4th word is: ' || v_word);

  -- Replace 'fox' with 'cat' and 'dog' with 'mouse'
  v_sentence := REPLACE(REPLACE(v_sentence, 'fox', 'cat'), 'dog', 'mouse');
  DBMS_OUTPUT.PUT_LINE('Modified sentence: ' || v_sentence);
END;

This example demonstrates:

1. Counting words by counting spaces
2. Extracting a specific word using SUBSTR and INSTR
3. Replacing multiple words in a sentence

Output:

Number of words: 9
The 4th word is: Fox
Modified sentence: The quick brown cat jumps over the lazy mouse

And there you have it! You've just taken your first steps into the wonderful world of PL/SQL strings. Remember, practice makes perfect, so don't be afraid to experiment with these functions and create your own string manipulation adventures.

Before you know it, you'll be stringing together complex queries and impressing your friends with your PL/SQL prowess. Keep coding, and happy string slinging!

Credits: Image by storyset