Getting e-mail domain name (without TLD) on SQL Server

Question

I am trying to get only domain name from Email1 column in Users table of my database.

UserId   Email1
  1      [email protected]
  2      [email protected]
  3      [email protected]
  4      [email protected]
  5      [email protected]

When I run this query:

SELECT LEFT( RIGHT(Email1, LEN(Email1)-CHARINDEX('@', Email1)),   
CHARINDEX('.', RIGHT(Email1, LEN(Email1)-CHARINDEX('@', Email1)))) 
as EmailNamePart FROM Users 

I get the following:

gmail.
google.
abc.
xyz.
stackoverflow.

while the expected result should be:

gmail
google
abc
xyz
stackoverflow

Any clue what is wrong in my query?

Answer 1

Try this:

-- if you only want to the first dot --

 select  SUBSTRING(Email1, CHARINDEX('@', Email1)+1,
 CHARINDEX('.', V.Email, CHARINDEX('@', Email1))-CHARINDEX('@', Email1)-1) as
 EmailNamePart FROM Users

-- if you want to the last dot --

select SUBSTRING(Email1, CHARINDEX('@', Email1)+1, 
(charindex('.', reverse(Email1) + '0') - len(Email1))*-1-(CHARINDEX('@', Email1)))
as EmailNamePart FROM Users