Strings in PL/SQL

MySQL String Functions and Operators

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;

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.

Tags:

#Databases