Home How to get email domain name from email ID in T-SQL assuming there is more than one delimited value?

Questions

How to get email domain name from email ID in T-SQL assuming there is more than one delimited value?

June 20, 2022

I have an email ID column and would like to extract the domain only accounting for sub domains if applicable. My current query is assuming the first delimited value only

For instance ‘abc@gmail.com’ = gmail

However, if the email is like ‘abc@gmail.co.com’ it will be extracted as gmail.co – I want it to be gmail only as well

My query:

SUBSTRING(col_email, 
    CHARINDEX('@', col_email) + 1, 
    LEN(col_email) - CHARINDEX('@', col_email) - CHARINDEX('.', REVERSE(col_email))) as domain

>Solution :

You can use a few of CHARINDEXs and SUBSTRING for this. This assumes that all email addresses are valid (so have a . after the @) and that you don’t have any outlier email addresses like Steve"@"home.has_a_valid_address@mydomain.com:

SELECT SUBSTRING(Email, CHARINDEX('@',Email)+1,CHARINDEX('.',Email,CHARINDEX('@',Email)) - CHARINDEX('@',Email) -1) AS YourDomain
FROM (VALUES('abc@gmail.com'))V(Email)