asked on

MysQL extract from an email address LEFT and RIGHT

What is the proper syntax to use to extract from an email address, stored in a column called "email": the information to the left of the @ symbol as one variable, and the information to the right of the the @ symbol until the first "." (some emails have .co.uk)?

so two variables:

$leftOf
$rightOf

thanks,

-hellenica

marcoullis

ASKER

oh and after this information is extracted i would like to combine them into a username, such that:

$username = $rightOf + $ leftOf + $id

the last variable is not a problem, its the primary key.

thanks.

-hellenica

friedrb

Do you want to do it in PHP or have the MySQL server do it?

Here's the MySQL:

create temporary table tmp
select 1 as id, 'wow@test.co.uk' as email   
         
select
  concat(
    substr(email,1,instr(email,'@')-1),
    substr(email,instr(email,'@')+1),
    id
  )  
  as username                  
from         
  tmp

Open in new window

OCDan

The above example would return wowtest.co.uk1

Is that what you want or would you need wowtest1?

marcoullis

ASKER

doesn't there need to be an "INSERT INTO" query?

marcoullis

ASKER

just the wowtest1

:-)

ASKER CERTIFIED SOLUTION

OCDan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

designatedinitializer

Here's a simple PHP solution for that:

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

$emailaccount = $parts[0];
$emaildomain = $parts[1];

Open in new window