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, second Beginning Mysql Database Design and Optimization: From Novice to Professional (Chad Russell, Jon Stephens, 2004, p. 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() the native function of Mysql() 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 within 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 a synonym for LOCATE(substr, str). With INSTR and LOCATE, although the description of the function is the same for the format INSTR(str, substr) vs LOCATE (substr, str) there is no mention of the implementation.
In
INSTRthere is: This is the same as the two-argument form of LOCATE(), except that the order of the Arguments is reversed. Seem to be the same.– Woss
@Andersoncarloswoss is very bizarre to have two equal functions hahahaha
– Sorack
I agree. I am also looking for something that justifies the existence of the two.
– Woss
I ran the tests on this one reply and I also got almost equal times, so probably one is not an optimized version of the other.
– Woss
You don’t want to write an answer with your conclusions?
– Sorack
Until I find anything conclusive, I don’t think it’s worth it.
– Woss
Apparently they first created the
INSTR(an abbreviation of "In String"), then wanted to evolve allowing passing a third argument indicating from which position to start the search, and created another method. They could have simply modified the originalINSTRwith the third optional argument (which would not affect existing calls), but perhaps they wanted to give a more intuitive name (easier to remember). Even the way methods treat the search case-sensitive is the same, it may even be that one internally calls the other, since there is no difference in performance.– Alisson
But the description of the two methods on the mysql website can lead someone to make a mistake. One says it returns the position, the other says it returns the index. Position and index are different things. By position, I would immediately understand that starts in 1, or zero if you don’t find. By index, I would understand that I would start at zero, or -1 if I didn’t find it. However, both methods have the same demeanor.
– Alisson