The LOCATE function returns a 64 bit signed integer containing the position of the specified string in a LONG VARCHAR column. LOCATE returns a 32 bit signed integer position for CHAR and VARCHAR columns.
LOCATE( long-varchar-column, string-expression [, numeric-expression ] )
long-varchar-column The name of the LONG VARCHAR column to search.
string-expression The string for which you are searching. This string is limited to 255 bytes.
numeric-expression The character position or offset at which to begin the search in the string. The numeric-expression is a 64 bit signed integer for LONG VARCHAR and LONG BINARY columns and is a 32 bit signed integer for CHAR, VARCHAR, and BINARY columns. The first character is position 1. If the starting offset is negative, LOCATE returns the last matching string offset, rather than the first. A negative offset indicates how much of the end of the string to exclude from the search. The number of characters excluded is calculated as ( -1 * offset ) - 1.
All the positions or offsets, returned or specified, in the LOCATE function are always character offsets and may be different from the byte offset for multibyte data.
If the LONG VARCHAR cell being searched contains more than one instance of the string:
If numeric-expression is specified, LOCATE starts the search at that offset in the string.
If numeric-expression is not specified, LOCATE returns only the position of the first instance.
If the column does not contain the string, the LOCATE function returns zero (0).
Searching for a string longer than 255 bytes returns NULL.
Searching for a zero-length string returns 1.
If any of the arguments is NULL, the result is NULL.
LOCATE also supports searching LONG BINARY columns.
For a full description and examples of the LOCATE function, see “LOCATE function [String]” in Chapter 4, “SQL Functions” of Reference: Building Blocks, Tables, and Procedures.
