Hi there,

I have a (seemingly) easy SQL problem that has me stumped. Any advice or suggestions would be GREATLY appreciated!!

I currently have a table email_data with a column of email address in it ('emailaddr'). I need to find a way to break down the e-mail addresses into 2 columns:

1: the username (what's before the @ sign)
2: the domain (what's after the @ sign)

Also, I would prefer not to lose the original column 'emailaddr' (the full email addresses).


Any ideas? Thank you so much for any help or assistance you can offer!

Regards,


Tyler

This should do it, assuming the table is named "the_table" and the field with email addresses is named "email".


SELECT LEFT(email, LOCATE("@",email)-1) AS user,
SUBSTRING(email, LOCATE("@",email)+1) AS domain
FROM the_table;

Thanks for the reply! Unfortunately, when I try your code, I'm getting an error:

'LOCATE' is not a recognized function name.

I'm running Microsoft SQL Server 2000... is LOCATE not an included function?

Thanks for your assistance!

Try using INSTR() instead. I don't have experience with MSSQL (we run MySQL). If you add fields for user and domain, you can probably populate the fields like this:


UPDATE email_data SET user=LEFT(emailaddr, INSTR(emailaddr,"@")-1),
domain=SUBSTRING(emailaddr, LOCATE(emailaddr,"@")+1);

Well, what if you exploded the email address when you inserted it into the database?

$parts = explode('@',$email);

Then insert the $parts[0] into the username and the $parts[1] into the domain. While still having the ability to insert the $email as the full email address that is not separated.

Does this help you at all?