getting domain extension in one statement

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Matija Lah SSCertifiable Points: 6429 More actions · Answer 1

Try this:

selectemails.email as emailAddress

,reverse(substring(reverse(emails.email), 1, charindex('.', reverse(emails.email)) - 1)) as emailDomain

from(

select'xxx.yyy@xxx.com' as email

union all

select'xxx.yyy@xxx.co.uk'

) emails

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Peter Schofield Ten Centuries Points: 1364 More actions · Answer 2

I've always used the following:

select substring(@myemail, patindex('%@%',@myemail)+1,len(@myemail)-patindex('%@%',@myemail)+1)

where @myemail holds the email address

so if @myemail="bill.gates@microsoft.com" then "microsoft.com" is returned.