LOCATE vs INSTR

Question

    SELECT INSTR('1234','2')  #returns 2
    SELECT LOCATE('2','1234') #returns 2

Besides the argument numbering are there any significant differences to be aware of before choosing either of the functions?

Answer 1

The INSTR() function starts the search from the first character.

The LOCATE() function has a third parameter which allows you to change the starting position.

-- returns 4
SELECT INSTR("Alibaba", "ba")

-- returns 4 because the third parameter was not specified
SELECT LOCATE("ba", "Alibaba")

-- returns 6
SELECT LOCATE("ba", "Alibaba", 5)

Answer 2

Locate

The LOCATE() function returns the position of the first occurrence of a substring in a string.

Instr

The INSTR() function returns the position of the first occurrence of a string in another string.

For example

SELECT LOCATE("H", "PHP") AS MatchPosition;`
-- -> returns 2
SELECT INSTR("PHP", "H") AS MatchPosition;
-- -> returns 2

And the performance is

-- 5.074 sec
SELECT BENCHMARK(100000000,INSTR('foobar','foo'));

-- 5.086 sec
SELECT BENCHMARK(100000000,LOCATE('foo','foobar'));