LOCATE_IN_STRING

The LOCATE_IN_STRING function returns the position at which an argument starts within a specified string.

>>-LOCATE_IN_STRING(source-string,search-string-+----------------------+-+--------------------+-)-><
                                                '-,start-+-----------+-' '-,--+-CODEUNITS16-+-'     
                                                         '-,instance-'        +-CODEUNITS32-+       
                                                                              '-OCTETS------'

The schema is SYSIBM.

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, 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. An optional string unit can be specified to indicate in what units the start and result of the function are expressed.

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 must return a value that is a built-in character string data type, graphic string data type, or binary string data type.

Start of change The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type. End of change

The expression can be specified by any of the following items:

A constant
A special register
A host variable (including a LOB locator variable or a file reference variable)
A scalar function whose arguments are any of the above (though nested function invocations cannot be used)
A CAST specification whose arguments are any of the above
A column name
An expression that concatenates (using CONCAT or ||) any of the above

search-string

An expression that specifies the string that is the object of the search. 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.

Start of change The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type. End of change

The expression can be specified by any of the following items:

A constant
A special register
A host variable (including a LOB locator variable or a file reference variable)
A scalar function whose arguments are any of the above (though nested function invocations cannot be used)
A CAST specification whose arguments are any of the above
A column name
An expression that concatenates (using CONCAT or ||) any of the above

These rules are similar to those that are described for pattern-expression for the LIKE predicate.

start

An expression that specifies the position within source-string at which the search is to start. The expression must return a value that is a built-in INTEGER or SMALLINT data type.

Start of change 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. End of change

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 the LENGTH(source-string) + start + 1 and continues for each position to the beginning of the string.

If start is not specified, the default is 1. If the value of the integer 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 INTEGER or SMALLINT data type. If instance is not specified, the default is 1. The value of the integer must be greater than or equal to one.

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.

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

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).

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

INSTR can be used as a synonym for LOCATE_IN_STRING.

Example 1: Find the position of an occurrence of the character 'N' in the string 'WINNING' by searching from the start of the string as measured in bytes, within the string.

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

Returns the values:

6	4	6	3	3	0