Db2 INSTR

Summary: in this tutorial, you will learn how to use the Db2 INSTR() to find the location of the nth occurrence of a substring within a string.

Db2 INSTR() function overview

The Db2 INSTR() function finds a substring in a string and returns the position of the nth occurrence of the substring.

The following shows the syntax of the Db2 INSTR() function:

INSTR(source_string , substring [, start_position [, occurrence]])
Code language: SQL (Structured Query Language) (sql)

The INSTR() function accepts four arguments:

source_string

Specifies the string that contains the substring which you want to search.

substring

Specifies the substring to be searched.

start_position

Is a non-zero positive integer that specifies the position in the string which the function start searching.

The start_position is optional. It defaults to 1, meaning the searching starts at the beginning of the source string.

instance

Is a positive integer that specifies which occurrence of the substring for which the INSTR() function should search.

The instance is optional. It defaults to 1, meaning that the INSTR() will search for the first occurrence of the substring.

Return value

If the substring is in the source string, the function returns a positive integer indicating the position of a substring within the source string. Otherwise, it returns 0.

Db2 INSTR() function examples

Let’s take some examples of using the INSTR() function.

1) Using Db2 INSTR() function to return the location of a substring within a string

This example uses the INSTR() function to return the location of the first occurrence of the substring 're' in the string 'There are some stores':

SELECT
  INSTR('There are some stores', 're' ) result
FROM
  sysibm.sysdummy1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

RESULT      
----------- 
4           
Code language: SQL (Structured Query Language) (sql)

The following example returns the second occurrence of the substring 're' in the string 'There are some stores':

SELECT
  INSTR( 'There are some stores', 're',1,2 ) result
FROM
  sysibm.sysdummy1;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

RESULT      
----------- 
8    
Code language: SQL (Structured Query Language) (sql)

2) Using INSTR() function to search for a substring which does not exist in a string

This example illustrates the result when the substring is not found in the source string:

SELECT
  INSTR( 'There are some stores', 'is') result
FROM
  sysibm.sysdummy1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

RESULT      
----------- 
0   
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 INSTR() function to return the position of the nth occurrence of a substring in a string.