DEV Community

Pranav Bakare
Pranav Bakare

Posted on

1 1 1 1 1

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!

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

đź‘‹ Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay