Skip to main content
Known Participant
October 7, 2010
해결됨

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

  • October 7, 2010
  • 2 답변들
  • 416 조회

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

이 주제는 답변이 닫혔습니다.
최고의 답변: David_Powers

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

2 답변

David_Powers
Inspiring
October 10, 2010

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작성자
Known Participant
October 10, 2010

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

Hugh

Participating Frequently
October 8, 2010

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

Try:

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