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.
- The first character of
string
is atposition
1. Ifposition
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
- If
position
is positive, then Oracle Database counts from the beginning ofstring
to find the first character; ifposition
is negative, then Oracle counts backward from the end ofstring
.1 2 3 4 5
SELECT substr('abcd',2) FROM dual --returns: bcd SELECT substr('abcd',-2) FROM dual --returns: cd
- 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.