LOCATE function is used to search for a string (the
needle) within another string (the haystack). If the desired string
LOCATE returns the index at which it is found. If the
desired string is not found,
LOCATE returns 0.
LOCATE ( CharacterExpression1, CharacterExpression2 [, StartPosition] )
A character expression that specifies the string to search for in CharacterExpression2, sometimes called the needle.
A character expression that specifies the string in which to search, sometimes called the haystack.
(Optional). Specifies the position in CharacterExpression2 at which
the search is to start. This defaults to the start of
CharacterExpression2, which is the value
The return type for
LOCATE is an integer that indicates 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,
- If the first argument is an empty string (
LOCATEreturns the value of the third argument (or
1if it was not provided), even if the second argument is also an empty string.
- If a
NULLvalue is passed for either of the CharacterExpression arguments,
splice> SELECT DisplayName, LOCATE('Pa', DisplayName, 3) "Position" FROM Players WHERE (INSTR(DisplayName, 'Pa') > 0) ORDER BY DisplayName; DISPLAYNAME |Position ------------------------------------ Alex Paramour |6 Buddy Painter |7 Jeremy Packman |8 Pablo Bonjourno |0 Paul Kaster |0 5 rows selected