DB2 Version 9.7 for Linux, UNIX, and Windows

LOCATE_IN_STRING scalar function


>>-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. The search is done using the collation of the database, unless search-string or source-string is defined as FOR BIT DATA, in which case the search is done using a binary comparison.

If the optional start is specified, it indicates the character position in the source-string at which the search is to begin. If the start is specified, an instance number can also be specified. The instance argument is used to determine the position of a specific occurrence of search-string within source-string. 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 string in which the search is to take place. The expression must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function. The expression can be specified in any of the following ways:

A constant
A special register
A global variable
A host variable (including a LOB locator variable or a file reference variable)
A scalar function
A large object locator
A column name
An expression that concatenates (using CONCAT or ||) any of the previous items

search-string

An expression that specifies the string that is the object of the search. The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BLOB, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, VARGRAPHIC or BLOB data type, it is implicitly cast to VARCHAR before evaluating the function. The actual length must not be greater than the maximum length of a VARCHAR. The search-string cannot be a BLOB file reference variable. The expression can be specified in any of the following ways:

A constant
A special register
A global variable
A host variable
A scalar function whose arguments are any of the previous items
An expression that concatenates (using CONCAT or ||) any of the previous items

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

start

An expression that specifies the position within source-string at which the search for a match is to start. The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC or VARGRAPHIC data type. 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 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 (SQLSTATE 42815).

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, CHAR, VARCHAR, GRAPHIC or VARGRAPHIC 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 (SQLSTATE 42815).

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit of start and the result. CODEUNITS16 specifies that start and the result are to be expressed in 16-bit UTF-16 code units. CODEUNITS32 specifies that start and the result are to be expressed in 32-bit UTF-32 code units. OCTETS specifies that start and the result are to be expressed in bytes.

If a string unit is specified as CODEUNITS16 or CODEUNITS32, and search-string or source-string is a binary string or bit data, an error is returned (SQLSTATE 428GC). If a string unit is specified as OCTETS and search-string and source-string are binary strings, an error is returned (SQLSTATE 42815).

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

If a locale-sensitive UCA-based collation is used for this function, then the CODEUNITS16 option offers the best performance characteristics.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see "String units in built-in functions" in "Character strings".

The first and second arguments must have compatible string types. For more information on compatibility, see "Rules for string conversions". In a Unicode database, if one string argument is character (not FOR BIT DATA) and the other string argument is graphic, then the search-string is converted to the data type of the source-string for processing. If one argument is character FOR BIT DATA, the other argument must not be graphic (SQLSTATE 42846).

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.

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.

INSTR can be used as a synonym for LOCATE_IN_STRING.

Examples

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, as measured in CODEUNITS32 units, within the string.
```
    SET :POSITION = LOCATE_IN_STRING('Jürgen lives on Hegelstraße',
                                    'ß',-1,CODEUNITS32);
```
The value of host variable POSITION is set to 26.
Find the location of the third occurrence of the character 'N' in the string 'WINNING' by searching from the start of the string and then set the host variable POSITION with the position of the character, as measured in bytes, within the string.
```
    SET :POSITION =
    LOCATE_IN_STRING('WINNING','N',1,3,OCTETS);
```
The value of host variable POSITION is set to 6.