LOCATE

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.

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.

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.

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

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.

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)

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.

For more information about LOCATE, see the description of POSITION.

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

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

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

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

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