CODEWITHSUNDEEP
sqlt-sqlemaildns
AMR1337
AMR1337

Reputation: 3

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

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

Upvotes: 0

Views: 1528

Answers (1)

Thom A
Thom A

Reputation: 96046

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)

Upvotes: 1

Related Questions