Getting a Substring in MySQL

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.


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

SUBSTRING_INDEX(email, '@', 1)


  1. Pingback: Getting a Substring in MySQL — jinahya – SutoCom Solutions

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s