SUBSTR function in Oracle

The SUBSTR function in Oracle SQL is used to extract a specific substring from a given string based on its position and length.

Syntax:

SUBSTR(string, start_position, length)

string: The source string from which the substring is to be extracted.

start_position: The position in the string where the extraction begins.

A positive number starts counting from the beginning of the string.

A negative number starts counting backward from the end of the string.

length (optional): The number of characters to extract. If omitted, the function will return the substring starting from start_position to the end of the string.

Examples:

1. Basic Usage

SELECT SUBSTR('Oracle SQL', 1, 6) AS result FROM dual;

-- Output: Oracle

1. Omitting the Length

SELECT SUBSTR('Oracle SQL', 8) AS result FROM dual;

-- Output: SQL

1. Using Negative Start Position

SELECT SUBSTR('Oracle SQL', -3, 3) AS result FROM dual;

-- Output: SQL

1. Extracting from Middle

SELECT SUBSTR('Oracle SQL', 3, 4) AS result FROM dual;

-- Output: acle

Use Cases:

1. Extract specific fields from delimited strings.




2. Extract substrings like year, month, or day from formatted date strings.




3. Retrieve part of a column value for data processing or filtering.

Let me know if you need further examples or specific use-case explanations!