Results 1 to 5 of 5
  1. #1

    Question SQL Question -- how to break down e-mail addresses into 'Username' and 'Domain'?

    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!



  2. #2
    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;

  3. #3
    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!

  4. #4
    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);

  5. #5
    Join Date
    Mar 2003
    South Bend, Indiana, USA
    Well, what if you exploded the email address when you inserted it into the database?

    PHP Code:
    $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?
    Daniel - php scripts and tutorials

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts