LOCATE_IN_STRING

The LOCATE_IN_STRING function returns the starting position of a 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.

If the optional start is specified, it indicates the character position in the source-string at which the search is to begin. If start is specified, an optional instance number can also be specified. The instance argument is used to determine the specific occurrence of search-string within source-string. Each unique instance can include any of the characters in a previous instance, but not all characters in a previous instance.

If the search-string has a length of zero, the result returned by the function is 1. If the source-string has a length of zero, the result returned by the function is 0. If neither condition exists, and if the value of search-string is equal to an identical length of a substring of contiguous positions within the value of source-string, the result returned by the function is the starting position of that substring within the source-string value; otherwise, the result returned by the function is 0.

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.

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.

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.

If the value of the integer is greater than zero, the search begins at start and continues for each position to the end of the string. If the value of the integer is less than zero, the search begins at CHARACTER_LENGTH(source-string) + start + 1 and continues for each position to the beginning of the string.

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

  POSITION(  search-string , source-string )

If start is zero, an error is returned.

instance

An expression that specifies which instance of search-string to search for within source-string. The expression must return a value that is a built-in numeric, character string, or graphic string data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. If instance is not specified, the default is 1. The value of the integer must be greater than or equal to 1.

At each search position, a match is found when the substring at that position and CHARACTER_LENGTH(search-string) - 1 values to the right of the search position in source-string, is equal to search-string.

The result of the function is a large integer. The result is the starting position of the instance of search-string within source-string. The value is relative to the beginning of the string (regardless of the specification of start).

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

The LOCATE_IN_STRING function operates on a character basis. Because LOCATE_IN_STRING 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_IN_STRING 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.

Syntax alternatives: INSTR can be used as a synonym for LOCATE_IN_STRING.

Example

Locate the character 'ß' in the string 'Jürgen lives on Hegelstraße' by searching from the end of the string, and set the host variable POSITION with the position within the string.
```
SET :POSITION = LOCATE_IN_STRING('Jürgen lives on Hegelstraße','ß',-1);
```
The value of host variable POSITION is set to 26.

Find the position of an occurrence of the character 'N' in the string 'WINNING' by searching from the start of the string.

SELECT LOCATE_IN_STRING('WINNING','N',1,3),     
       LOCATE_IN_STRING('WINNING','N',3,2),
       LOCATE_IN_STRING('WINNING','N',3,3)
FROM SYSIBM.SYSDUMMY1;

Returns the values:

6     4     6

Find the position of an occurrence of the character 'N' in the string 'WINNING' by searching from the end of the string.

SELECT LOCATE_IN_STRING('WINNING','N',-1,3), 
       LOCATE_IN_STRING('WINNING','N',-3,2), 
       LOCATE_IN_STRING('WINNING','N',-3,3)
FROM SYSIBM.SYSDUMMY1;

Returns the values:

3     3     0