Oracle SUBSTR Function Explained with Examples

The Oracle SUBSTR function is used to get a smaller string (the substring) from within a larger string. Let’s take a look at how you can use it and some examples.

 

Purpose of the Oracle SUBSTR Function

This Oracle SUBSTR function allows you to extract a smaller string from within a larger string.

The smaller string is called the substring, which is where the name of the SUBSTR function comes from (SUBSTRing)

Why is this useful?

It’s one of the many string manipulation functions in Oracle, and it’s a common feature of many programming languages. It’s good for splitting data and getting the part you need from a longer string.

 

SUBSTR Syntax and Parameters

The syntax of the SUBSTR function is:

SUBSTR (string, start_position, [length] )

The parameters of the SUBSTR function are:

• string (mandatory): This is the base string value that the substring is obtained from.
• start_position (mandatory): This is the starting position of the substring within the string. It’s where the substring starts from. The first position is always 1.
• length (optional): This is the number of characters to extract from string, to create the substring. If it is not specified, the function returns the entire string.

As mentioned, the start_position starts at 1. There are a couple of variations that can be used for this parameter:

• If start_position is 0, it is treated as 1
• If start_position is positive (greater than 0), the function counts the position from the beginning of the string to find the starting character
• if start_position is negative (less than 0), the function counts the position from the end of the string, and goes backwards, to find the starting character

For the length parameter, it should be a number greater than or equal to 1. If you specify a value less than 1, the function returns NA.

 

Substring Function Variations

This function is different from other functions in that it has several versions that can be run, depending on the way that the length is calculated:

• SUBSTR calculates the length using characters defined by the input character set.
• SUBSTRB calculates the length using bytes.
• SUBSTRC calculates the length using Unicode complete characters.
• SUBSTR2 calculates the lengths using USC2 code points.
• SUBSTR4 calculates the lengths using USC4 code points.

The INSTR function also has similar variations.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

 

How Can I Use Oracle SUBSTR To Remove the Last Character?

The best way to use Oracle SUBSTR to remove the last character is using a combination of SUBSTR and LENGTH.

The LENGTH function can be used to determine the length parameter of the function. Remember, functions can contain other functions as parameters.

This function would be:

SUBSTR ( string, 0, LENGTH( string) - n)

In this case, n can be set to 1 as you want to remove the last character. It can be set to another value if you want more characters removed.

For example, to remove the last character from the name of this site, “Database Star”, the function would be:

SUBSTR ("Database Star", 0, LENGTH("Database Star") - 1)

This would return:

“Database Sta”

See the Examples section below for more examples.

 

How Can Oracle SUBSTR and INSTR Be Used Together?

The SUBSTR and INSTR functions can be used together to get a specific string up until the occurrence of another character or string.

This is good for when you need to extract part of a string in a column, but the length is varied.

You would use the INSTR function as the length parameter:

SUBSTR (string, 1, INSTR(string, substring, 1, 1))

Also, you can use it as part of the start_position parameter if you want to start from the occurrence of a specific character.

For example, if names are stored as “lastname, firstname”, you can use this method to extract the firstname from the value.

SUBSTR (string, INSTR(string, substring, 1, 1))

See the Examples section below for more examples.

 

How Can I Use Oracle SUBSTR In Reverse or SUBSTR From The Right?

To use SUBSTR in reverse, otherwise known as using SUBTSR from the right, simply specify a negative number as the start_position.

To start immediately from the right, use the value of -1.

To start a specific number of characters from the right, use a lower value (e.g. -5 for the fifth character).

This will start counting backwards from the right of the string.

 

Can I Use Oracle SUBSTR with CLOB?

You can use this function with CLOB and NCLOB data types.

However, Oracle has an inbuilt package called DBMS_LOB which is better at handling these data types, which they recommend using.

DBMS_LOB.SUBSTR performs similar functionality for CLOBs and LOBs.

 

Can I Use Oracle SUBSTR with a LONG?

No, unfortunately, you can’t perform this function on a LONG.

According to Oracle, from version 8.0 you should be using the CLOB data type instead.

The only way that I know of to get a SUBSTR from a LONG variable is to write a PL/SQL procedure that takes a ROWID, then converts that to a 32k variable, and returns 4000 characters to SQL, which can then be used for the SUBSTR function.

This is described in more detail here.

 

SUBSTR Data Types

The string parameter can be any of the following data types:

• CHAR
• VARCHAR2
• NCHAR
• NVARCHAR2
• CLOB
• NCLOB

Both start_position and length need to be a NUMBER datatype, or a datatype that can be implicitly converted to a NUMBER and must resolve to an integer. This means it can be a different data type but needs to be a whole number.

The return value of the Oracle SUBSTR function is always the same data type as the one provided for string. So, if STRING is a VARCHAR2, the function returns VARCHAR2.

 

Examples of the SUBSTR Function

Here are some examples of the Oracle SUBSTR function. I find that examples are the best way for me to learn about code, even with the explanation above.

These strings can be shown along with their positions:

 

1	2	3	4	5	6	7	8	9	10	11	12	13
D	a	t	a	b	a	s	e		S	t	a	r

 

Example 1:

This is an example using both parameters for the SUBSTR.

SELECT 'Database Star',
SUBSTR('Database Star', 1, 10) AS SUB
FROM DUAL;

Result:

DATABASESTAR	SUB
Database Star	Database S

This shows the first 10 characters from the string.

 

Example 2:

This example only uses the start_position parameter and not the length.

SELECT 'Database Star',
SUBSTR('Database Star', 5) AS SUB
FROM DUAL;

Result:

DATABASESTAR	SUB
Database Star	base Star

This shows the string starting at position 5.

 

Example 3:

This example shows the last character of the string.

SELECT 'Database Star',
SUBSTR('Database Star', -1, 5) AS SUB
FROM DUAL;

Result:

DATABASESTAR	SUB
Database Star	r

As the start_position is -1, it starts at the first character before the end of the string. Because the length is greater than 1, it returns the whole substring.

 

Example 4:

This example shows the last 5 characters of the string.

SELECT 'Database Star',
SUBSTR('Database Star', -5, 5) AS SUB
FROM DUAL;

Result:

DATABASESTAR	SUB
Database Star	Star

Because the start position is -5, it starts at the 5th character from the right of the string.

 

Example 5:

This example shows how both SUBSTR and INSTR can be used together.

SELECT 'Database Star',
SUBSTR('Database Star',
INSTR('Database Star', ' ', 1, 1)) AS SUB
FROM DUAL;

Result:

DATABASESTAR	SUB
Database Star	Star

This shows the value of the string starting from the first space character.

 

Example 6:

This example uses the INSTR as a length parameter.

SELECT 'Database Star',
SUBSTR('Database Star', 1,
INSTR('Database Star', ' ', 1, 1)) AS SUB
FROM DUAL;

Result:

DATABASESTAR	SUB
Database Star	Database

This shows the string up until the first space character. It’s good for finding the first word in a string.

 

Example 7:

This example shows how to find the middle of a string using a space character and INSTR.

SELECT 'Once upon a...',
SUBSTR('Once upon a...',
INSTR('Once upon a...', ' ', 1, 1)+1,
INSTR('Once upon a...', ' ', 1, 2)-INSTR('Once upon a...', ' ', 1, 1)-1) AS SUB
FROM DUAL;

Result:

ONCEUPONA…	SUB
Once upon a…	upon

It’s a bit more complicated, but the INSTR is used to find the first space, then find the second space, and return the string in between using SUBSTR.

 

Example 8:

This example shows how to remove the last character using SUBSTR.

SELECT 'Database Star',
SUBSTR('Database Star', 0,
LENGTH('Database Star') - 1) AS SUB
FROM DUAL;

Result:

DATABASESTAR	SUB
Database Star	Database Sta

The last character has been removed from the string.

 

Similar Functions

Some functions which are similar to the SUBSTR function are:

• REGEXP_SUBSTR – Similar to the SUBSTR function, but allows for regular expressions.
• INSTR – This allows you to search for a string within another string.
• LENGTH – Returns the length of the provided string.
• REPLACE – Replaces one string with another string in the provided value.

You can find a full list of Oracle functions here.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: