LOCATE

The LOCATE function returns the starting position of the first occurrence of one string (called the search-string) within another string (called the source-string). If the search-string is not found and neither argument is null, the result is zero. If the search-string is found, the result is a number from 1 to the actual length of the source-string. If the optional start is specified, it indicates the character position in the source-string at which the search is to begin.

search-string

An expression that specifies the string that is to be searched for. search-string may be any built-in numeric, datetime, or string expression. It must be compatible with the source-string. A numeric or datetime argument is cast to a character string before evaluating the function. For more information about converting numeric and datetime to a character string, see VARCHAR.

source-string

An expression that specifies the source string in which the search is to take place. source-string may be any built-in numeric, datetime, or string expression. A numeric or datetime argument is cast to a character string before evaluating the function. For more information about converting numeric and datetime to a character string, see VARCHAR.

start

An expression that specifies the position within source-string at which the search is to start. start may be any built-in numeric, character string, or graphic string expression. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The value must be greater than zero.

If start is specified, the function is similar to:

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

If start is not specified, the function is equivalent to:

    POSITION( search-string , source-string ) )

For more information, see POSITION.

The result of the function is a large integer. If any of the arguments can be null, the result can be null; if any of the arguments is null, the result is the null value.

The LOCATE function operates on a character basis. Because LOCATE operates on a character-string basis, any shift-in and shift-out characters are not required to be in exactly the same position and their only significance is to indicate which characters are SBCS and which characters are DBCS.

If the CCSID of the search-string is different than the CCSID of the source-string, it is converted to the CCSID of the source-string.

If a collating sequence other than *HEX is in effect when the statement that contains the LOCATE function is executed and the arguments are SBCS data, mixed data, or Unicode data, then the result is obtained by comparing weighted values for each value in the set. The weighted values are based on the collating sequence. An ICU collating sequence table may not be specified with the LOCATE function.

Example

Select RECEIVED and SUBJECT columns as well as the starting position of the words 'GOOD' within the NOTE_TEXT column for all entries in the IN_TRAY table that contain these words.
```
  SELECT RECEIVED, SUBJECT, LOCATE('GOOD', NOTE_TEXT)
    FROM IN_TRAY
    WHERE LOCATE('GOOD', NOTE_TEXT) <> 0
```
Assume that NOTE is a VARCHAR(128) column, encoded in Unicode UTF-8, that contains the value 'Jürgen lives on Hegelstraße'. Find the character position of the character 'ß' in the string.
```
  SELECT LOCATE( 'ß', NOTE ), POSSTR( NOTE_TEXT, 'ß')
    FROM T1
```
Returns the value 26 for LOCATE and 27 for POSSTR.