Oracle SUBSTR function
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:
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises