Oracle SUBSTR Function: A Simple Guide

The SUBSTR function (short for "Substring") is one of the most powerful and commonly used string functions in Oracle SQL. It allows you to extract or "cut out" a specific piece of a string.

You can tell it exactly where to start, and exactly how many characters to grab.

What is the SUBSTR Function in Oracle?

The SUBSTR function lets you get a part of any string. This is essential for formatting data, cleaning up text, or isolating specific information.

This is useful for:

• Extracting the first initial from a name.
• Getting the last 4 digits of a credit card number.
• Pulling an area code from a phone number.
• Isolating a specific segment of a standardized product ID.

SUBSTR Function Syntax

The syntax for SUBSTR is as follows:

SUBSTR(char, position, [substring_length])

Let's break that down:

• char: The original string or column you want to cut (e.g., 'Hello World').
• position: An integer for the character where you want to start extracting.
  • If position is positive (like 1), it counts from the beginning of the string. (Note: The first character is always position 1, not 0).
  • If position is negative (like -3), it counts from the end of the string.
• [substring_length] (Optional): The number of characters you want to extract.
  • If you omit this, SUBSTR will return all characters from the position to the very end of the string.

Oracle SUBSTR Function Examples

Here are two practical examples of how to use SUBSTR.

Example 1: Extracting from the Beginning (Positive Position) with SUBSTR

Let's say you have a list of employee IDs that follow the format DEPT-NUMBER (e.g., SALES-1024). You want to extract just the 4-digit number.

The number starts at position 7.

Query:

SELECT 
  SUBSTR('SALES-1024', 7) AS "Employee Number"
FROM DUAL;

(Since we omitted substring_length, it grabs everything from the 7th character to the end.)

Result:

Employee Number
---------------
1024

Example 2: Extracting from the End (Negative Position) using SUBSTR

Imagine you want to get the last 3 letters of a product code to find its region, but the codes have different lengths (e.g., X-100-USA and LONG-Y-40-CAN).

You can use a negative position to start counting from the end.

Query:

-- Start 3 characters from the end, and grab 3 characters
SELECT 
  SUBSTR('X-100-USA', -3, 3) AS "Region 1",
  SUBSTR('LONG-Y-40-CAN', -3, 3) AS "Region 2"
FROM DUAL;

Result:

Region 1  Region 2
--------  --------
USA       CAN