Solved

MysQL extract from an email address LEFT and RIGHT

Posted on 2012-03-31
7
425 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

808 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