Solved

MysQL extract from an email address LEFT and RIGHT

Posted on 2012-03-31
7
447 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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 

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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

705 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