PL/SQL Strings

A string in PL/SQL is a sequence of characters (letters, numbers, spaces or symbols).PL/SQL provides three types of strings:

Fixed-Length Strings (CHAR): Have a predefined size and occupy the full allocated space, padded with spaces if needed.
Variable-Length Strings (VARCHAR2): Do not reserve full space initially and can store up to 32,767 characters.
CLOB (Character Large Objects): Used to store very large text data beyond the limits of standard string 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

Function	Description
ASCII()	Return numeric value of left-most character.
BIN()	Return a string containing binary representation of a number.
BIT_LENGTH()	Return length of argument in bits.
CHAR()	Return the character for each integer passed.
CHAR_LENGTH()	Return number of characters in argument.
CONCAT()	Return concatenated string.
CONCAT_WS()	Return concatenated string with separator.
ELT()	Return string at index number.
EXPORT_SET()	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string.
FIELD()	Index (position) of first argument in subsequent arguments.
FIND_IN_SET()	Index (position) of first argument within second argument.
FORMAT()	Return a number formatted to specified number of decimal places.
FROM_BASE64()	Decode base64 encoded string and return result.
HEX()	Hexadecimal representation of decimal or string value.
INSERT()	Insert substring at specified position up to specified number of characters.
INSTR()	Return the index of the first occurrence of substring.
LCASE()	Converts to lowercase.
LEFT()	Return the leftmost number of characters as specified.
LENGTH()	Return the length of a string in bytes.
LIKE	Simple pattern matching.
LOAD_FILE()	Load the named file.
LOCATE()	Return the position of the first occurrence of substring.
LTRIM()	Remove leading spaces.
MAKE_SET()	Return a set of comma-separated strings that have the corresponding bit in bits set.
MATCH()	Perform full-text search.
MID()	Return a substring starting from the specified position.
NOT LIKE	Negation of simple pattern matching.
OCT()	Return a string containing octal representation of a number.
ORD()	Return character code for leftmost character of the argument.
QUOTE()	Escape the argument for use in an SQL statement.
REGEXP	Whether string matches regular expression.
REGEXP_INSTR()	Starting index of substring matching regular expression.
REGEXP_LIKE()	Whether string matches regular expression.
REGEXP_REPLACE()	Replace substrings matching regular expression.
REGEXP_SUBSTR()	Return substring matching regular expression.
REPEAT()	Repeat a string the specified number of times.
REPLACE()	Replace occurrences of a specified string.
REVERSE()	Reverse the characters in a string.
RIGHT()	Return the specified rightmost number of characters.
RLIKE()	Whether string matches regular expression.
RTRIM()	Remove trailing spaces.
SPACE()	Return a string of the specified number of spaces.
STRCMP()	Compare two strings.
SUBSTR()	Return the substring as specified.
TRIM()	Remove leading and trailing spaces.
UPPER()	Converts to uppercase.

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:  Worl

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'.