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:

NameDescription
search strA string which will be searched for.
strA string which is going to be searched.
positionPosition 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.

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

Leave a Reply