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

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 '[email protected]' = gmail

However, if the email is like '[email protected]' 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

CodePudding user response：

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"@"[email protected]:

SELECT SUBSTRING(Email, CHARINDEX('@',Email) 1,CHARINDEX('.',Email,CHARINDEX('@',Email)) - CHARINDEX('@',Email) -1) AS YourDomain
FROM (VALUES('[email protected]'))V(Email)