CODEWITHSUNDEEP
sqlsql-server
cybercore
cybercore

Reputation: 13

Extract first and last name from email address. Microsoft SQL Server

I am really new to the SQL environment. I am now facing a problem that I can't get solved.

I have a field email in which are the email addresses with the following format [email protected]

I now need a select that splits the email into first name, last name so like this.

[email protected]

to

Firstname Lastname 
marc      mueller

I was able to filter out the first name but the last name does not work.

select email,
       LEFT(email,CHARINDEX('.', email)-1) AS [New Firstname]
from data

Can someone pls point me in the right direction? many greetings

Upvotes: 1

Views: 5587

Answers (3)

SMor
SMor

Reputation: 2862

And another alternative using parsename. CTEs are used to help "see" the logic flow but it can all be converted into a more compact form.

declare @x table (email varchar(200));
insert @x (email) values ('[email protected]'), ('[email protected]')
;

with cte_replace as (select replace(email, '@', '.') as p1 
    from @x
), 
cte_split as (select parsename(p1, 4) as p2, parsename(p1, 3) as p3 
    from cte_replace
)
select * from cte_split;

Notice how the logic handles a name without a period to separate first/last parts. An alternative to the REPLACE usage is to simple truncate from the @ sign.

Upvotes: 0

Austin
Austin

Reputation: 2265

Assuming we always have a single . separating first and last name, and will not appear anywhere else in the value, and also will only have a single @ separating name and domain:

select left(left(email, CHARINDEX('@', email) - 1), CHARINDEX('.', email) - 1) first_name
    , right(left(email, CHARINDEX('@', email) - 1), len(left(email, CHARINDEX('@', email) - 1)) - CHARINDEX('.', email)) last_name
from tbl

Edit:

Here is a case statement to handle a missing . in the name:

select left(left(email, CHARINDEX('@', email) - 1), CHARINDEX('.', email) - 1) first_name
    , case when email like '%.%@%' then right(left(email, CHARINDEX('@', email) - 1), len(left(email, CHARINDEX('@', email) - 1)) - CHARINDEX('.', email)) else left(left(email, CHARINDEX('@', email) - 1), CHARINDEX('.', email) - 1) end last_name
from tbl

However, we have no way of knowing if it is the first or last name, so this will return the name in both columns.

Upvotes: 1

Ogün Birinci
Ogün Birinci

Reputation: 728

Maybe you can try substring function according to the index of "." and "@"

SELECT
SUBSTRING(email,0,charindex('.',email)) as firstname,
SUBSTRING(email,charindex('.',email)+1,charindex('@',email)-charindex('.',email)-1) as lastname FROM data

Firstly, we split the email address according to the "." character and we got the firstname. Secondly, we split the email address starting from the first "." character to the "@" character.

Remember substring syntax; SUBSTRING(string, start, length)

So to find the length of the lastname sub string, we should subtract index of "@" from "."

Upvotes: 3

Related Questions