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!

Sentry blog image

Identify what makes your TTFB high so you can fix it

In the past few years in the web dev world, we’ve seen a significant push towards rendering our websites on the server. Doing so is better for SEO and performs better on low-powered devices, but one thing we had to sacrifice is TTFB.

Read more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay