Oracle SUBSTR
Introduction
In this tutorial we are going to explains how to use Oracle SUBSTR function with basic syntax and examples.
SUBSTR function extracts a sub-string from a string.
Syntax:
SUBSTR( string, start_position [, length ] )| Name | Description | Data Types |
|---|---|---|
| char | A string from which a sub-string is to be returned. | CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The exceptions are SUBSTRC, SUBSTR2, and SUBSTR4, which do not allow char to be a CLOB or NCLOB. |
| start_position | An integer indicating a string position within the string char. | NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer. |
| length | An integer indicating a number of characters to be returned. | NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer. |
Note:
- If start_position is 0, then sub-string function treats start_position as number of 1.
- If start_position is a positive number, then sub-string function starts from the beginning of character given.
- If start_position is a negative number, then sub-string function starts from the end of character given and counts backwards.
- If length is omitted, then function return all characters till the end of character given.
- If length is less then 1, then function return null.
Examples:
Let’s get some examples about Oracle SUBSTR and how to use it:
SELECT SUBSTR('It is Oraask.com',7,6)
FROM DUAL;
SELECT SUBSTR('It is Oraask.com',7)
FROM dual;
SELECT SUBSTR('It is Oraask.com',-10,6)
FROM dual;
Similar functions to Oracle SUBSTR:
INSTR – return the location of the string in number position .
TRIM – delete space or specified character from start , end or both of a string.
In this tutorial, you have learned how to use the function to extracts a sub-string from a string. by giving 3 different examples for better understanding.
Hopefully, it was clear and concise.
awesome…. well explained… thanks a lot.
it’s very simple and into the point. thanks
clear and concise
Nicely written… thanks !