Oracle SUBSTR

SUBSTR is an Oracle String function used to extract characters from a string.

Syntax

Here’s the syntax of the Oracle SUBSTR function ().

SUBSTR( Expression, x [, y])

Here,

Expression = It is a string or a column name from which we want to extract characters. The data type of this expression must be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

x = x is the starting position from which we want to begin extraction

Here, if the value of x is positive then it starts extracting characters from that position. If it is zero, then extraction begins from first position and if it is negative then it begins from backwards.

y = y is the number of characters we want to extract after the starting position.

If we don’t enter a value for y, then it returns all characters. Suppose, we give y=2 in query then Oracle will extract 2 characters from the starting position.

The y can’t be less than 1, otherwise it will return NULL.

Lets understand this through examples.

SUBSTR Function Examples

1. Extracting Characters from a simple string

We will use a simple query to extract a few characters from a string, ‘Welcome to Qurosity Blog’. Let’s have a look:

SELECT
SUBSTR( 'Welcome to Qurosity Blog.', 1, 7 ) SUBSTRING
FROM
dual;

Result:

Here, 1 = Starting position from when we begin extraction.

7 = Total No. of letters that we want to extract

So, starting from first position we extract 7 characters and the query returns > Welcome

Lets have a look at one more example using the same string, but changing the values of x and y.

Here we enter the negative value of x, which means that Oracle will count the start position from backwards. And, y is the number of characters we want to extract.

SELECT SUBSTR( 'Welcome to Qurosity Blog.', -14, 9 ) SUBSTRING
FROM
Dual;

Result:

Here, -14 = Starting position from end of string ( Because value is negative)

And, 9 = No. of letters to extract.

The Query returns > Qurosity

Lets have a look at another example:

SELECT SUBSTR( 'Welcome to Qurosity Blog.', 9 ) SUBSTRING 
FROM
dual;

Result:

Here, 9 = Starting position from where Oracle begins extraction and since we don’t give value of y to specify how many characters to return, it returns all characters after starting position.

2. Extracting characters from values in a column

Here, we will using Employees Table from HR schema for this example:

Have a look at this simple query, where we extract first 5 characters from a column in a table:

SELECT SUBSTR(first_name, 1, 5) 
FROM employees ;

Result:

Here, 1 = Starting Position

5 = Total no. of characters to extract.

The query returns first 5 characters starting from first position from first_name column in employees table.

Lets see one more example, where we combine SUBSTR and CONCAT function to extract first character from first_name and last_name columns and then we combine them to fetch abbreviation of name of each employee in the table.

Lets have a look at the query:

SELECT ( SUBSTR(first_name, 1, 1) || '.' || SUBSTR(last_name, 1, 1) || '.' ) Abbreviation
FROM employees ;

Result:

The query first finds the first character from first_name column and also the first character from last_name column.

Then it, concatenates them using concatenation operator ( || ) and adding the ‘ . ‘ after the end of each character.

The query returns the abbreviation of all employees working in the organization.

In this article, we learned how to use the SUBSTR function to extract characters from a string or a column.