CODEWITHSUNDEEP
mysqlsql
iks_in
iks_in

Reputation: 133

Extract email id from field mysql

I have a column First_Name. But it has names along with email ids. eg. '[email protected] Bobby' and sometimes like this, 'Bobby [email protected]'

I want to fetch email id from first_name column.

I tried this way:

select left(First_Name,locate(' ',First_Name)-1)
from mytable
where First_Name like '%gmail%' and Email_Personal=""

But this works in only first case where email id is first while in second case where name is first, it fetches name. I also want to remove email id after storing it into another column -Email_Personal. So, in other words, I want to fetch email id from first_name and want to store it into email_personal and keep only name in the first_name.

I've also tried this way:

select substring_index(first_name,' ',1) 
from mytable
where First_Name like '%gmail%' and Email_Personal1=""

But this also worked for the first case only. Where there is name first then, it returns only name not email id.

Upvotes: 0

Views: 629

Answers (2)

Alex H
Alex H

Reputation: 300

Alternatively you could find the @ and get all text until first space in both directions:

select 
concat (
  reverse(substring_index(substring(reverse(First_Name), -- Bit before @
    instr(reverse(First_Name),'@')+1),' ',1)),
  substring_index(substring(First_Name,                  -- Bit after @
    instr(First_Name,'@')),' ',1) 
)
from mytable

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

If you always have a 1-word first name, you can do:

select (case when substring_index(first_name, ' ', 1) like '%@%'
             then substring_index(first_name, ' ', -1)
             else substring_index(first_name, ' ', 1)
         end) as RealFirstName

You can use similar logic to extract the email address:

select (case when substring_index(first_name, ' ', 1) like '%@%'
             then substring_index(first_name, ' ', 1)
             else substring_index(first_name, ' ', -1)
         end) as EmailAddress

Upvotes: 1

Related Questions