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).
>>-LOCATE--(--search-string--,--source-string--+----------+-----> '-,--start-' >--+--------------------+--)----------------------------------->< '-,--+-CODEUNITS16-+-' +-CODEUNITS32-+ '-OCTETS------'
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 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.
These rules are similar to those that are described for pattern-expression for the LIKE predicate.
POSITION(search-string,
SUBSTRING(source-string, start, string-unit),
string-unit) + start - 1
where string-unit is
either CODEUNITS16, CODEUNITS32, or OCTETS. POSITION(search-string, source-string, string-unit)
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 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.
SELECT LOCATE('N', 'DINING')
FROM SYSIBM.SYSDUMMY1
The result is the value
3. 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)
The
value of host variable LOCATION is set to 26. SET :LOCATION = LOCATE('ß', 'Jürgen lives on Hegelstraße', 1, CODEUNITS16)
The
value of host variable LOCATION is set to 26. SET :LOCATION = LOCATE('ß', 'Jürgen lives on Hegelstraße', 1, OCTETS)
The
value of host variable LOCATION is set to 27.'&' | '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' |
SELECT LOCATE('~', UTF8_VAR, CODEUNITS16),
LOCATE('~', UTF8_VAR, CODEUNITS32),
LOCATE('~', UTF8_VAR, OCTETS)
FROM SYSIBM.SYSDUMMY1
returns the values 4,
3, and 6, respectively. SELECT LOCATE('~', UTF16_VAR, CODEUNITS16),
LOCATE('~', UTF16_VAR, CODEUNITS32),
LOCATE('~', UTF16_VAR, OCTETS)
FROM SYSIBM.SYSDUMMY1
returns the values 4,
3, and 7, respectively.SET :LOCATION = LOCATE('Brown', 'The quick brown fox', CODEUNITS16)
The
value of the host variable LOCATION is set to 11.