Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

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
0
marcoullis
Asked:
marcoullis
1 Solution
 
marcoullisAuthor Commented:
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
0
 
friedrbCommented:
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

0
 
OCDanCommented:
The above example would return wowtest.co.uk1

Is that what you want or would you need wowtest1?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
marcoullisAuthor Commented:
doesn't there need to be an "INSERT INTO" query?
0
 
marcoullisAuthor Commented:
just the wowtest1

:-)
0
 
OCDanCommented:
You can select this straight from your users table provided the user field is called email, if not change all instances of email to the field name.
SELECT
concat(
    substr(email,1,instr(email,'@')-1),
   substr(email,instr('@',email)+1 ,instr('.',email)-1-instr('@',email)),
    id
  ) as 'Email', otheruserfields if needed
FROM
Users
0
 
designatedinitializerCommented:
Here's a simple PHP solution for that:

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

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

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now