LOCATE scalar function

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

The schema is SYSIBM. The SYSFUN version of the LOCATE function continues to be available, but it is not sensitive to the database collation.

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 a binary string or 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. 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. Otherwise, if the source-string has a length of zero, the result returned by the function is 0. Otherwise:

If the value of search-string is equal to an identical length of substring of contiguous positions within the value of source-string, the result returned by the function is the starting position of the first such substring within the source-string value.
Otherwise, the result returned by the function is 0.

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, binary string, numeric, Boolean, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, or binary string data type, it is implicitly cast to VARCHAR before evaluating the function. The expression cannot be specified by a LOB file reference variable.

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, Boolean, or datetime data type. If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function.

startAn 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 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. The value of the integer must be greater than or equal to zero. If start is specified, the LOCATE function is similar to:

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

where string-unit is either CODEUNITS16, CODEUNITS32, or OCTETS.

If start is not specified, the search begins at the first position of the source string, and the LOCATE function is similar to:

   POSITION(search-string, source-string, string-unit)

If OCTETS is specified and source-string is graphic data, the value of the integer must be odd (SQLSTATE 428GC).

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 FOR BIT DATA, an error is returned (SQLSTATE 428GC). If the string unit is specified as CODEUNITS16 or OCTETS, and the string units of source-string is CODEUNITS32, an error is returned (SQLSTATE 428GC).

If a string unit is not explicitly specified and if source-string is a character or graphic string, the string units of source-string determines the unit that is used for the result and for start (if specified). 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 about 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).

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

Examples

Example 1: Find the location of the first occurrence of the character N in the string DINING.
```
   SELECT LOCATE('N', 'DINING')
   FROM SYSIBM.SYSDUMMY1
```
The result is the value 3.
Example 2: For all the rows in the table named IN_TRAY, select the RECEIVED column, the SUBJECT column, and the starting position of the string GOOD within the NOTE_TEXT column.
```
   SELECT RECEIVED, SUBJECT, LOCATE('GOOD', NOTE_TEXT)
   FROM IN_TRAY
   WHERE LOCATE('GOOD', NOTE_TEXT) <> 0
```
Example 3: Locate the character ß in the string Jürgen lives on Hegelstraße, and set the host variable LOCATION with the position, as measured in CODEUNITS32 units, within the string.
```
   SET :LOCATION = LOCATE('ß', 'Jürgen lives on Hegelstraße', 1, CODEUNITS32)
```
The value of host variable LOCATION is set to 26.
Example 4: Locate the character 'ß in the string Jürgen lives on Hegelstraße, and set the host variable LOCATION with the position, as measured in CODEUNITS16 units, within the string.
```
   SET :LOCATION = LOCATE('ß', 'Jürgen lives on Hegelstraße', 1, CODEUNITS16)
```
The value of host variable LOCATION is set to 26.
Example 5: Locate the character ß in the string Jürgen lives on Hegelstraße, and set the host variable LOCATION with the position, as measured in OCTETS, within the string.
```
   SET :LOCATION = LOCATE('ß', 'Jürgen lives on Hegelstraße', 1, OCTETS)
```
The value of host variable LOCATION is set to 27.
Example 6: The following examples work with the Unicode string &N~AB, where & is the musical symbol G clef character, and ~ is the non-spacing combining tilde character. This string is shown in different Unicode encoding forms in the following example:

& N ~ A B

UTF-8 X'F09D849E' X'4E' X'CC83' X'41' X'42'

UTF-16BE X'D834DD1E' X'004E' X'0303' X'0041' X'0042'
Assume that the variable UTF8_VAR contains the UTF-8 representation of the string.
```
   SELECT LOCATE('~', UTF8_VAR, CODEUNITS16),
     LOCATE('~', UTF8_VAR, CODEUNITS32),
     LOCATE('~', UTF8_VAR, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values 4, 3, and 6, respectively.
Assume that the variable UTF16_VAR contains the UTF-16BE representation of the string.
```
   SELECT LOCATE('~', UTF16_VAR, CODEUNITS16),
      LOCATE('~', UTF16_VAR, CODEUNITS32),
      LOCATE('~', UTF16_VAR, OCTETS)
   FROM SYSIBM.SYSDUMMY1
```
returns the values 4, 3, and 7, respectively.
Example 7: In a Unicode database created with the case insensitive collation CLDR181_LEN_S1, find the position of the word Brown in the phrase The quick brown fox.
```
SET :LOCATION = LOCATE('Brown', 'The quick brown fox', CODEUNITS16)
```
The value of the host variable LOCATION is set to 11.

	&	N	~	A	B
UTF-8	X'F09D849E'	X'4E'	X'CC83'	X'41'	X'42'
UTF-16BE	X'D834DD1E'	X'004E'	X'0303'	X'0041'	X'0042'