Introduction:

When it comes to managing large amounts of data in Oracle databases, developers and database administrators need to be familiar with various SQL functions. One such function is the Oracle DBMS_LOB.SUBSTR function, which allows you to extract a portion of a large object (LOB) value. This function is useful when working with text, images, video, or other types of binary data.

In this article, we’ll take a closer look at the Oracle DBMS_LOB.SUBSTR function and how it can be used in SQL queries. We’ll cover its syntax, parameters, and examples of how to use this function in various scenarios.

What is the Oracle DBMS_LOB.SUBSTR Function?

The Oracle DBMS_LOB.SUBSTR function is a built-in function in the Oracle database that allows you to extract a portion of a LOB value. This function takes three parameters: the LOB column, the length of the substring to be extracted, and the starting position of the substring.

The syntax of the Oracle DBMS_LOB.SUBSTR function is as follows:

Syntax

DBMS_LOB.SUBSTR (
   lob_column    IN    CLOB or NCLOB or BLOB or BFILE,
   amount        IN    INTEGER,
   offset        IN    INTEGER DEFAULT 1
) RETURN VARCHAR2 CHARACTER SET lob_column%CHARSET; 

Let’s take a closer look at each of these parameters.

Parameters of the Oracle DBMS_LOB.SUBSTR Function

The Oracle DBMS_LOB.SUBSTR function takes the following parameters:

lob_column: This parameter specifies the LOB column from which you want to extract the substring. This can be a CLOB, NCLOB, BLOB, or BFILE column.

amount: This parameter specifies the length of the substring to be extracted. The length can be specified as an integer value.

offset: This parameter specifies the starting position of the substring. The starting position can be specified as an integer value. If this parameter is not specified, the default value is 1.

RETURN VARCHAR2 CHARACTER SET lob_column%CHARSET: This parameter specifies the character set of the substring that is returned. It is always set to the character set of the LOB column.

Examples of Using the Oracle DBMS_LOB.SUBSTR Function

Let’s look at some examples of how to use the Oracle DBMS_LOB.SUBSTR function in SQL queries.

Example 1: Extracting a Substring from a CLOB Column

Suppose you have a table called “employees” with a CLOB column called “employee_resume.” You want to extract the first 100 characters of the employee_resume column for all employees.

SELECT employee_name, DBMS_LOB.SUBSTR(employee_resume, 100) AS resume_excerpt
FROM employees; 

Output:

In this example, we used the DBMS_LOB.SUBSTR function to extract the first 100 characters of the employee_resume column for each employee.

Example 2: Extracting a Substring from a BLOB Column

Suppose you have a table called “images” with a BLOB column called “image_data.” You want to extract the first 50 bytes of the image_data column for all images.

SELECT image_name, DBMS_LOB.SUBSTR(image_data, 50) AS image_excerpt
FROM images;

Output:

In this example, we used the DBMS_LOB.SUBSTR function to extract the first 50 bytes of the image_data column for each image.

Example 3: Extracting a Substring with a Specific Offset

Suppose you have a table called “products” with a CLOB column called “product_description.” You want to extract a substring starting from the 10th character and with a length of 50 characters for all products.

SELECT product_name, DBMS_LOB.SUBSTR(product_description, 50, 10) AS prod_description_excerpt
FROM products; 

Output:

In this example, we used the DBMS_LOB.SUBSTR function to extract a substring starting from the 10th character and with a length of 50 characters from the product_description column for each product.

FAQs About the Oracle DBMS_LOB.SUBSTR Function

Q: What is a LOB column?

A: A LOB column is a data type in Oracle databases that can store large amounts of data, such as text, image, video, or other types of binary data.

Q: Can the Oracle DBMS_LOB.SUBSTR function be used with any type of LOB column?

A: Yes, the Oracle DBMS_LOB.SUBSTR function can be used with any type of LOB column, including CLOB, NCLOB, BLOB, and BFILE columns.

Q: How do I specify the character set of the returned substring?

A: The character set of the returned substring is always set to the character set of the LOB column.

Final Words:

In this article, we’ve covered the Oracle DBMS_LOB.SUBSTR function and how it can be used in SQL queries. We’ve looked at its syntax, parameters, and examples of how to use this function in various scenarios. Whether you’re working with text, image, video, or other types of binary data, the Oracle DBMS_LOB.SUBSTR function can help you extract the data you need with ease. Also, if you want to know more about this function go to oracle sql reference from here. So go ahead and give it a try in your next SQL query!