What is the difference between the LOCATE and INSTR functions?

In MySQL there are 3 ways to return the first occurrence of a substring in a string:

LOCATE(substr,str), LOCATE(substr,str,pos)
INSTR(str,substr)
POSITION(substr IN str)

In addition to LOCATE allow the search start position parameter, according to Beginning MySQL Database Design and Optimization: From Novice to Professional (Chad Russell, Jon Stephens, 2004: 208):

  

POSITION () is standard SQL, LOCATE () is specific to MySQL (), INSTR () is   supported for compatibility with ORACLE.

That is: POSITION () would be the default mysql, LOCATE () MySQL native function () and INSTR () would be present to maintain compatibility with ORACLE banks.

And everything indicates that they have the same implementation, since Item_func_instr extends the Item_func_locate class inside the MySQL source code in sql / item_func.h:

class Item_func_instr : public Item_func_locate
{
public:
  Item_func_instr(const POS &pos, Item *a, Item *b) :Item_func_locate(pos, a, b)
  {}

  const char *func_name() const { return "instr"; }
};

It is important to note that in the MySQL manual, POSITION (substr IN str) appears as synonymous with LOCATE (substr, str). However, with INSTR and LOCATE, although the function description is the same for the INSTR (str, substr) vs LOCATE (substr, str) format, there are no mentions about the implementation.

The difference is that in LOCATE you can tell from which position you want to find the substring in the string. Example:

INSTR('she sells seashells', 's') -> RETORNA 1

LOCATE('s', 'she sells seashells', 3) -> RETORNA 5

LOCATE('s', 'she sells seashells', 6) -> RETORNA 9

LOCATE('s', 'she sells seashells', 15) -> RETORNA 19

Locale accepts a third parameter, this is to inform you from which position you want to find the term in the string

Mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
Mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
Mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7

Source: link