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.

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

Your email address will not be published. Required fields are marked *

%d bloggers like this: