Oracle SUBSTR function

Last update on August 19 2022 21:50:41 (UTC/GMT +8 hours)

Description

The SUBSTR functions returns the specified number (substring_length) of characters from a particular position of a given string. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.

If the position is 0, then it is treated as 1.
If the position is positive, then Oracle Database counts from the beginning of char to find the first character.
If the position is negative, then Oracle counts backward from the end of char.
If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.

Syntax:

{ SUBSTR
| SUBSTRB
| SUBSTRC
| SUBSTR2
| SUBSTR4
}
(char, position [, substring_length ])

Parameters:

Name	Description	Data Types
char	A string from which a substring 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.
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.
substring_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.

Return Value Type

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

Applies to

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Pictorial Presentation

Examples: Oracle SUBSTR function

The following example returns several specified substrings of "w3resource":

SELECT SUBSTR('w3resource',3,4) "Substring"
FROM DUAL;

Sample Output:

Substring
---------
reso

SELECT SUBSTR('w3resource',-5,4) "Substring"
FROM DUAL;

Sample Output:

 Substring
---------
ourc

Assume a double-byte database character set:

Previous: SOUNDEX
Next: TRANSLATE