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.

Wait.

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

SUBSTRING_INDEX(email, '@', 1)

2 comments

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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