PL/SQL Strings

PL/SQL Triggers

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

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

'w3wiki' 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 concatenate with separator.
ELT()	Return string at index number.
EXPLORE_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.
FOMRAT()	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: 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'.

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.

Tags:

#Databases