In Oracle, the SUBSTR() function returns a substring from a given string.

SUBSTR() requires at least two arguments; the string, and the position for which to extract the substring from. It also accepts an optional third argument that allows you to specify how long the substring should be.

SUBSTR() can also be thought of as a group of functions. There are five separate functions; SUBSTR(), SUBSTRB(), SUBSTRC(), SUBSTR2(), and SUBSTR4(). Each function calculates the length in a different way.

Syntax

The syntax goes like this:

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

Where char is the string, position is the starting position of the substring, and substring_length is the length of characters to extract.

The functions calculate lengths as follows:

Function	Calculates length using…
SUBSTR()	Characters as defined by the input character set
SUBSTRB()	Bytes
SUBSTRC()	Unicode complete characters
SUBSTR2()	UCS2 code points
SUBSTR4()	UCS4 code points

Example

Here’s a basic example:

SELECT SUBSTR('Big fat cat', 5)
FROM DUAL;

Result:

fat cat

Compared with SUBSTRB()

This example shows how the results can differ, depending on which specific function you’re using, and the character set involved.

In this case, we compare SUBSTR() with SUBSTRB():

SELECT 
    SUBSTR('Böyük yağlı pişik', 5) AS SUBSTR,
    SUBSTRB('Böyük yağlı pişik', 5) AS SUBSTRB
FROM DUAL;

Result:

          SUBSTR           SUBSTRB 
________________ _________________ 
k yağlı pişik    ük yağlı pişik   

We can see that the two functions returned two different results. This is because some characters in this string use two bytes.

The SUBSTR() function returns the length in characters as defined by the input character set, whereas the SUBSTRB() function returns the length in bytes.

If we return to the original string, the results are the same between the two functions:

SELECT 
    SUBSTR('Big fat cat', 5) AS SUBSTR,
    SUBSTRB('Big fat cat', 5) AS SUBSTRB
FROM DUAL;

Result:

    SUBSTR    SUBSTRB 
__________ __________ 
fat cat    fat cat   

That’s because this string uses just one byte per character, and so the length in bytes is the same as the number of characters.

Substring Length

Here’s an example that specifies the length of the substring to extract:

SELECT SUBSTR('Big fat cat', 5, 3)
FROM DUAL;

Result:

fat

And here’s a comparison between SUBSTR() and SUBSTRB() when specifying the length on multi-byte characters:

SELECT 
    SUBSTR('Böyük yağlı pişik', 5, 9) AS SUBSTR,
    SUBSTRB('Böyük yağlı pişik', 5, 9) AS SUBSTRB
FROM DUAL;

Result:

      SUBSTR    SUBSTRB 
____________ __________ 
k yağlı p    ük yağl    

Zero Position

One quirk of this function is that, passing a position of 0 produces the same result as passing 1:

SELECT 
    SUBSTR('Big fat cat', 0, 3) AS "0",
    SUBSTR('Big fat cat', 1, 3) AS "1"
FROM DUAL;

Result:

     0      1 
______ ______ 
Big    Big   

Negative Position

Specifying a negative value for the position causes the starting position to be counted backwards from the end of the string:

SELECT SUBSTR('Big fat cat', -3)
FROM DUAL;

Result:

cat

And any length that is specified is counted from that position forward:

SELECT SUBSTR('Big fat cat', -7, 3)
FROM DUAL;

Result:

fat

Null Arguments

If any (or all) of the arguments are null, the result is null:

SET NULL 'null';
SELECT 
    SUBSTR(null, 3, 3) AS r1,
    SUBSTR('Coffee', null, 3) AS r2,
    SUBSTR('Coffee', 3, null) AS r3,
    SUBSTR(null, null, null) AS r4
FROM DUAL;

Result:

     R1      R2      R3      R4 
_______ _______ _______ _______ 
null    null    null    null   

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling SUBSTR() without passing any arguments results in an error:

SELECT SUBSTR()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SUBSTR()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing too many arguments also results in an error:

SELECT SUBSTR('Big fat cat', 3, 2, 1)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SUBSTR('Big fat cat', 3, 2, 1)
FROM DUAL
Error at Command Line : 1 Column : 36
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: