Solved

MysQL extract from an email address LEFT and RIGHT

Posted on 2012-03-31
7
437 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 500 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

733 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