I just learned how to get substring of specific range using LOCATE() and SUBSTRING().

Let’s say we have email addresses and want to get username part from them.

Step 1 Locate the index of ‘@’ character

LOCATE('@', email)

Step 2 Get substring using it.

SUBSTRING(email, 1, LOCATE('@', email) - 1)

Note that the starting index is 1 and the the len part must be 1 minus the location.

Wait.

There is a function exactly does this. SUBSTRING_INDEX().

SUBSTRING_INDEX(email, '@', 1)