The LOCATE function is used to search for a string within
another string. If the desired string is found,
LOCATE returns the index at which it is found.
If the desired string is not found, LOCATE returns 0.
Syntax
LOCATE(CharacterExpression, CharacterExpression [, StartPosition] )
There are two required arguments to the LOCATE function,
and a third optional argument.
- The first CharacterExpression specifies the string to
search for.
- The second CharacterExpression specifies
the string in which to search.
- The third argument is the startPosition, and specifies
the position in the second argument at which the search is
to start. If the third argument is not provided, the LOCATE
function starts its search at the beginning of the second
argument.
The return type for LOCATE is an integer. The LOCATE function
returns an integer indicating the index position within the
second argument at which the first argument was first located. Index
positions start with 1.
If the first argument is not found in the second argument, LOCATE
returns 0. If the first argument is an empty string (''), LOCATE
returns the value of the third argument (or 1 if it was not provided),
even if the second argument is also an empty string. If a NULL
value is passed for either of the CharacterExpression arguments,
NULL is returned.
-- returns 2, since 'love' is found at index position 2:
VALUES LOCATE('love', 'clover')
-- returns 0, since 'stove' is not found in 'clover':
VALUES LOCATE('stove', 'clover')
-- returns 5 (note the start position is 4):
VALUES LOCATE('iss', 'Mississippi', 4)
-- returns 1, because the empty string is a special case:
VALUES LOCATE('', 'ABC')
-- returns 0, because 'AAA' is not found in '':
VALUES LOCATE('AAA', '')
-- returns 3
VALUES LOCATE('', '', 3)
