mysql locate function
MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start. If this position is not mentioned, searching starts from the beginning.
Syntax:
LOCATE(substr,str)
or
LOCATE (search str, str, [position])
Arguments:
| Name | Description |
|---|---|
| search str | A string which will be searched for. |
| str | A string which is going to be searched. |
| position | Position from where (within the second argument) the searching will start . |
Example:
mysql> SELECT LOCATE(‘b’, ‘blog.eduguru.in’);
Result: 1
mysql> SELECT LOCATE(‘B’, ‘blog.eduguru.in’);
Result: 1
mysql> SELECT LOCATE(‘g’, ‘blog.eduguru.in’, 5);
Result: 9
mysql> SELECT LOCATE(‘o’, ‘blog.eduguru.in’);
Result: 3
mysql> SELECT LOCATE(‘u’, ‘eduguru.in’, 4);
Result: 5
mysql> SELECT LOCATE(‘guru’, ‘eduguru.in’, 1);
Result: 3
mysql> SELECT LOCATE(‘p’, ‘blog.eduguru.in’);
Result: 0
Point to be Noted:
- The first syntax returns the position of the first occurrence of substring substr in string str.
- The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos.
- Returns 0 if substr is not in str.
