The LOCATE function returns the location index value of a substring within a character string. The third argument position can be omitted. If this argument is specified, the function searches for substring from the given position and returns the location index value of the first occurrence. If the substring cannot be found within the string, 0 is returned.
The LOCATE function is working like the POSITION Function, but you cannot use LOCATE for bit strings.
LOCATE ( substring, string [, position] )
string :
• character string
• NULL
--it returns 1 when substring is empty space
SELECT LOCATE ('', '12345abcdeabcde');
locate('', '12345abcdeabcde')
===============================
1
--it returns position of the first 'abc'
SELECT LOCATE ('abc', '12345abcdeabcde');
locate('abc', '12345abcdeabcde')
================================
6
--it returns position of the second 'abc'
SELECT LOCATE ('abc', '12345abcdeabcde', 8);
locate('abc', '12345abcdeabcde', 8)
======================================
11
--it returns 0 when no substring found in the string
SELECT LOCATE ('ABC', '12345abcdeabcde');
locate('ABC', '12345abcdeabcde')
=================================
0