In this tutorial, we will learn a very useful function available in Oracle for manipulating character strings. The function name is SUBSTR . It is a short form for SubString and also pronounced as substring.

This function is widely used to extract a substring from another string.

Its syntax is

SUBSTR(s, p, [l])

Where

s: It's the string from which you want to extract characters(sub-string)

p: It's the position in the string from where you want to extract characters

l: [optional], The number of characters you want to return from the position p. If you omit the length then
               SUBSTR function will return the rest of the characters starting from the position p till the end of the string s.

SUBSTR() Examples

Now let's start understanding the usage of this function.

For example, we want to return the first 5 characters from the string "United States". To achieve this we will give the following command

SELECT SUBSTR("United States",1,5) FROM dual;

SUBST
---------
Unite

To get first 3 characters from employee names in emp table, we will give the following query

SELECT ename,SUBSTR(ename,1,3) from emp;

Similarly, to get last 3 characters employee names, give the following query

SELECT ename,SUBSTR(ename,-3) FROM emp;

To get the employee name from 4th position to till the end, we can use the SUBSTR function like this

SELECT ename,SUBSTR(ename,4) from emp;

To extract 2 characters from string "ABCDEFGH IJK"

SELECT SUBSTR('ABCDEFGH IJKL',5,2) FROM DUAL;

SUBST
---------
EF

In this tutorial we have seen how to use SUBSTR function to extract characters from a given string. There is also another function, known as INSTR() which is used to check whether a substring occurs in a given string.

See also INSTR() function with examples

NVL() function in Oracle

Oracle SQL Functions