query to extract as far as the @ symbol in email addresses

[hughanagle]

Hello.

I'd like to create a MySQL query to extract everything 'on the left' of the @ symbol in a set of email addresses, plus the @ symbol itself, plus an ellipsis.

In other words, if johndoe@example.com was in the list, I'd like the query to return just this:

johndoe@…

That is, returning just the first part of the email address but omitting the host.

Would anyone like to oblige me by telling me what that query would be?

Thanks in advance for your help.

Hugh

[bregent]

The MySQL reference document is your friend: http://dev.mysql.com/doc/

Try:

LEFT (MyColumn, LOCATE( '@',MyColumn))

[David_Powers · Accepted solution]

hughanagle wrote:

I'd like to create a MySQL query to extract everything 'on the left' of the @ symbol in a set of email addresses, plus the @ symbol itself, plus an ellipsis.

SELECT CONCAT(SUBSTRING_INDEX(email, '@', 1), '...') AS trimmed_email
FROM mytable

[hughanagle]

Thanks bregent. Thanks David. Your help is much appreciated.

Hugh