LOCATE
Purpose
This function returns the position in string at which search_string appears. If this is not contained, the value 0 is returned.
Syntax
locate::=
Usage Notes
-
The optional parameter position defines the search direction and at which position the search shall start:
-
If position is positive, the string is searched from the beginning to the end, starting at the character position counted from the beginning (the first character has position 1).
-
If position is negative, the string is searched from the end to the beginning, starting at character position counted from the end (the last character has position -1). The return value is counted from the beginning to the end, so the function will never return a negative value.
Example: LOCATE(string,'abc',-3) will search backwards starting from the third last character in the string.
-
-
LOCATE(string,search_string) is similar to LOCATE(string,search_string,1).
Example
SELECT LOCATE('cab','abcabcabc') LOCATE1,
LOCATE('user','user1,user2,user3,user4,user5', -1) LOCATE2;
Result
LOCATE1 | LOCATE2 |
3 | 25 |