LOCATE scalar function

The LOCATE function returns the position at which the first occurrence of an argument starts within another argument.

The schema is SYSIBM.

The LOCATE function returns the starting position of search-string within source-string. If search-string is not found and neither argument is null, the result is zero. If search-string is found, the result is a number from 1 to the actual length of source-string. If search-string has a length of zero, the result returned by the function is 1. If the optional start is specified, it indicates the character position in source-string at which the search is to begin. An optional string unit can be specified to indicate in what units the start and result of the function are expressed.

search-string

An expression that specifies the string that is to be searched for. search-string must return a value that is a built-in character string data type, graphic string data type, or binary string data type with an actual length that is no greater than 4000 bytes.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

source-string

An expression that specifies the source string in which the search is to take place. source-string must return a value that is a built-in character string data type, graphic string data type, or binary string data type.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

start

An expression that specifies the position within search-string where the search is to start.

start is expressed in the specified string unit and must return an integer value that is greater than or equal to zero.

The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34) which is then assigned to an INTEGER value.

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit that is used to express start and the result. If source-string is a character string that is defined as bit data, CODEUNITS16 and CODEUNITS32 cannot be specified. If source-string is a graphic string, OCTETS cannot be specified. If source-string is a binary string, CODEUNITS16, CODEUNITS32, and OCTETS cannot be specified.

CODEUNITS16: Specifies that start and the result are expressed in terms of 16-bit UTF-16 code units.
CODEUNITS32: Specifies that start and the result are expressed in terms of 32-bit UTF-32 code units.
OCTETS: Specifies that start and the result are expressed in terms of bytes.

If a string unit is not explicitly specified, the data type of the result determines the string unit that is used. If the result is graphic data, start and the returned position are expressed in two-byte units; otherwise, they are expressed in bytes.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String unit specifications.

The first and second arguments must have compatible string types. For more information on compatibility, see Conversion rules for comparisons.

The result of the function is a large integer.

The result can be null; if any argument is null, the result is the null value.

Notes

Similar functions:

If start is specified, the LOCATE function is similar to the following POSITION function, where string-units is CODEUNITS16, CODEUNITS32, or OCTETS:

   POSITION(search-string,
                 SUBSTRING(source-string, start, string-units)) + start - 1

If start is not specified, the search begins at the first position of source-string and the LOCATE function is similar to the following POSITION function, where string-units is CODEUNITS16, CODEUNITS32, or OCTETS:

   POSITION(search-string, source-string, string-units)

For more information, see POSITION scalar function.

Examples

Example 1:

Find the location of the first occurrence of the character 'N' in the string 'DINING'.

   SELECT LOCATE('N', 'DINING')
     FROM SYSIBM.SYSDUMMY1;

The result is the value 3.

Example 2:

For all the rows in the table named IN_TRAY, select the RECEIVED column, the SUBJECT column, and the starting position of the string 'GOOD' within the NOTE_TEXT column.

   SELECT RECEIVED, SUBJECT, LOCATE('GOOD', NOTE_TEXT)
     FROM IN_TRAY
     WHERE LOCATE('GOOD', NOTE_TEXT) <> 0;

Example 3:

Locate the character 'ß' in the string 'Jürgen lives on Hegelstraße', and set the host variable LOCATION with the position, as measured in CODEUNITS32 units, within the string.

   SET :LOCATION = LOCATE('ß','Jürgen lives on Hegelstraße',1,CODEUNITS32);

The value of host variable LOCATION is set to 26.

Example 4:

Locate the character 'ß' in the string 'Jürgen lives on Hegelstraße', and set the host variable LOCATION with the position, as measured in CODEUNITS16 units, within the string.

   SET :LOCATION = LOCATE('ß','Jürgen lives on Hegelstraße',1,CODEUNITS16);

The value of host variable LOCATION is set to 26.

Example 5:

Locate the character 'ß' in the string 'Jürgen lives on Hegelstraße', and set the host variable LOCATION with the position, as measured in OCTETS, within the string.

   SET :LOCATION = LOCATE('ß','Jürgen lives on Hegelstraße',1,OCTETS);

The value of host variable LOCATION is set to 27.