Db2 LOCATE

Summary: in this tutorial, you will learn how to use the Db2 LOCATE() function to return the position at which the first occurrence of a string starts within another string.

Introduction to Db2 LOCATE() function

The LOCATE() function returns the position at which the first occurrence of a substring starts within another string.

The following illustrates the syntax of the LOCATE() function:

LOCATE(search_string,source_string,start,string_unit)
Code language: SQL (Structured Query Language) (sql)

In this syntax, the function returns the starting position of the first occurrence of the search_string within the source_string.

If any argument is NULL, the function returns NULL.

search_string

Specify the string to be searched within the source_string.

• If the search_string is not found, the function returns zero.
• If the search_string has a length of zero, the function return 1.
• If the search_string found in the source_string, the function returns a result that ranges from 1 to the actual length of the source_string.

 source_string

Specify the source_string from which to find the search_string.

 start

Is an optional integer that specifies the position in the source_string at which the search is to begin.

If you omit the start, the search begins at the beginning of the source_string.

 string_unit

Is an optional string unit that expresses in what units the start of the result of the function. The string_unit can be one of the following value CODEUNITS16, CODEUNITS32, and OCTETS.

Db2 LOCATE() function examples

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

1) Using Db2 LOCATE() function to find a string in another string

This example uses the LOCATE() function to find the first occurrence of the string 'is' in the string 'This is the LOCATE function':

 SELECT
      LOCATE('is','This is the LOCATE function')
 FROM
      SYSIBM.SYSDUMMY1;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

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

2) Using Db2 LOCATE() function with table data example

The following statement finds books whose title contains the 'Java' keyword:

 SELECT 
      title, 
      LOCATE('Java',title) java_location
 FROM
      books
 WHERE 
      LOCATE('Java',title) <> 0;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

3) Using Db2 LOCATE() function with string unit example

This example uses the string unit as CODEUNITS32 to find the character ß in the string Maßſtab:

 SELECT 
      LOCATE('ß','Maßſtab',1,CODEUNITS32) result
 FROM
      SYSIBM.SYSDUMMY1;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

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

In this tutorial, you have learned how to use the Db2 LOCATE() function to find the first occurrence of a string within another string.