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