FB

Oracle SUBSTR Function Explained with ExamplesThe 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

The syntax of the SUBSTR function is:

SUBSTR (string, start_position, [length] )

I’ll explain what each of these parameters mean below.

Also, 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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

SUBSTR Parameters

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

Also, 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.

 

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

The best way to use Oracle SUBSTR to remove 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, “Complete IT Professional”, the function would be:

SUBSTR ("Complete IT Professional", 0, LENGTH("Complete IT Professional") - 1)

This would return:

“Complete IT Professiona”

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, simple 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 14 15 16 17 18 19 20 21 22 23 24
C o m p l e t e I T P r o f e s s i o n a l

 

Example 1:

This is an example using both parameters for the SUBSTR.

SELECT 'Complete IT Professional',
 SUBSTR('Complete IT Professional', 1, 10) AS SUB
 FROM DUAL;

Result:

‘COMPLETEITPROFESSIONAL’ SUB
Complete IT Professional Complete I

This shows the first 10 characters from the string.

 

Example 2:

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

SELECT 'Complete IT Professional',
 SUBSTR('Complete IT Professional', 5) AS SUB
 FROM DUAL;

Result:

‘COMPLETEITPROFESSIONAL’ SUB
Complete IT Professional lete IT Professional

This shows the string starting at position 5.

 

Example 3:

This example shows the last character of the string.

SELECT 'Complete IT Professional',
 SUBSTR('Complete IT Professional', -1, 5) AS SUB
 FROM DUAL;

Result:

‘COMPLETEITPROFESSIONAL’ SUB
Complete IT Professional l

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 'Complete IT Professional',
 SUBSTR('Complete IT Professional', -5, 5) AS SUB
 FROM DUAL;

Result:

‘COMPLETEITPROFESSIONAL’ SUB
Complete IT Professional ional

Because 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 'Complete IT Professional',
 SUBSTR('Complete IT Professional',
 INSTR('Complete IT Professional', ' ', 1, 1)) AS SUB
 FROM DUAL;

Result:

‘COMPLETEITPROFESSIONAL’ SUB
Complete IT Professional  IT Professional

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 'Complete IT Professional',
 SUBSTR('Complete IT Professional', 1,
 INSTR('Complete IT Professional', ' ', 1, 1)) AS SUB
 FROM DUAL;

Result:

‘COMPLETEITPROFESSIONAL’ SUB
Complete IT Professional Complete

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 'Complete IT Professional',
 SUBSTR('Complete IT Professional',
 INSTR('Complete IT Professional', ' ', 1, 1)+1,
 INSTR('Complete IT Professional', ' ', 1, 2)-INSTR('Complete IT Professional', ' ', 1, 1)-1) AS SUB
 FROM DUAL;

Result:

‘COMPLETEITPROFESSIONAL’ SUB
Complete IT Professional IT

It’s a bit more complicated, but the INSTR is used to find the first space, and 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 'Complete IT Professional',
 SUBSTR('Complete IT Professional', 0,
 LENGTH('Complete IT Professional') - 1) AS SUB
 FROM DUAL;

Result:

‘COMPLETEITPROFESSIONAL’ SUB
Complete IT Professional Complete IT Professiona

The last character has been removed from the string.

 

Example 9:

This example shows how you can remove more than the last character (in this case, remove the last 5 characters).

SELECT 'Complete IT Professional',
 SUBSTR('Complete IT Professional', 0,
 LENGTH('Complete IT Professional') - 5) AS SUB
 FROM DUAL;

Result:

‘COMPLETEITPROFESSIONAL’ SUB
Complete IT Professional Complete IT Profess

This string has had the last 5 characters removed from it.

 

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 – 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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your Free PDF: 9 Ways to Improve your Database Skills

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your FREE PDF: 9 Ways to Improve your Database Skills