The Oracle SUBSTR SQL Function is very common, and many times misused.

Below shows the SUBSTR function along with the arguments it takes:

1

substr(string, position)

1

substr(string, position, substring_length)

The Oracle SUBSTR function returns a portion of string, beginning at character position, substring_length characters long.

Following are important rules to follow along with syntax exemplifying the implications of the rules.

1. The first character of string is at position 1. If position is 0, then it is treated as 1.
   

   1 2 3 4 5

   SELECT substr('abcd',1) FROM dual --returns: abcd   SELECT substr('abcd',0) FROM dual --returns: abcd

2. If position is positive, then Oracle Database counts from the beginning of string to find the first character; if position is negative, then Oracle counts backward from the end of string.

   1 2 3 4 5

   SELECT substr('abcd',2) FROM dual --returns: bcd   SELECT substr('abcd',-2) FROM dual --returns: cd

3. If substring_length is omitted, then Oracle returns all characters to the end of string. If substring_length is less than 1, then Oracle returns null.

   1 2 3 4 5 6 7 8

   SELECT substr('abcd',2) FROM dual --returns: bcd   SELECT substr('abcd',2,1) FROM dual --returns: b   SELECT substr('abcd',2,0) FROM dual --returns: [null]

string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Both position and substring_length must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer. The return value is the same datatype as string.

SUBSTR is most powerful and often used in practice with the Oracle INSTR SQL function. Here are some examples of how to combine SUBSTR with INSTR.

Related Information:

1. Oracle INSTR Function - SQL Syntax Examples

   The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming...

2. Oracle SUBSTR with INTSR Function - SQL Syntax Examples

   Oracle SUBSTR and INSTR SQL functions are typically used together in practice for parsing a string. Following are...

3. Oracle TO_DATE Function - SQL Syntax Examples

   The Oracle TO_DATE SQL function is used to convert a TEXT or NTEXT representation of a date into an Oracle...

4. SQL LIKE - Oracle LIKE Operator - SQL Syntax Examples

   The reserved word LIKE in Oracle SQL represents an operator that performs some basic matching on text. LIKE provides a...

5. Oracle TO_CHAR Function - SQL Syntax Examples (Most With Dates, TO_DATE)

   This article provides common examples of TO_CHAR using dates....

6. XMLFOREST vs XMLELEMENT - Missing vs Empty in SQLX

   Like me, some people may have gotten excited about using XMLFOREST in SQLX, only to find that NULL values result...

7. Oracle SQL Subselect Statements

   It is becoming more common that I find myself surprised by self-proclaimed, seasoned SQL developers who do not even understand...

8. Thoughts on Learning Oracle SQL: 1 - The Prelude

   So you want to learn SQL. Not a problem. You want to learn Oracle SQL, not a problem. You have...