?
Solved

MysQL extract from an email address LEFT and RIGHT

Posted on 2012-03-31
7
Medium Priority
?
453 Views
Last Modified: 2012-03-31
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
Comment
Question by:marcoullis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Author Comment

by:marcoullis
ID: 37791594
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
 
LVL 4

Expert Comment

by:friedrb
ID: 37791646
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
 
LVL 9

Expert Comment

by:OCDan
ID: 37791658
The above example would return wowtest.co.uk1

Is that what you want or would you need wowtest1?
0
Technology Partners: 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!

 

Author Comment

by:marcoullis
ID: 37791660
doesn't there need to be an "INSERT INTO" query?
0
 

Author Comment

by:marcoullis
ID: 37791661
just the wowtest1

:-)
0
 
LVL 9

Accepted Solution

by:
OCDan earned 2000 total points
ID: 37791696
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
 
LVL 7

Expert Comment

by:designatedinitializer
ID: 37791919
Here's a simple PHP solution for that:

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

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

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question