Extract extension from email address in SQL

Question

I wanted to extract the extension from email address.

Input: test@work.com
Output: com

Input: test@work.test.com
Output: test.com

I tried,

(REVERSE(LEFT(REVERSE('test@work.test.com'), CHARINDEX('.', REVERSE('test@work.test.com')) - 1))) 

This works only the first input. Any help?

Answer 1

It seems you want to remove any characters prior to and including the first period (.) after the at symbol (@). I would use CHARINDEX and STUFF for this:

SELECT STUFF(V.Email,1,CHARINDEX('.',V.Email,CHARINDEX('@',V.Email)),'')
FROM (VALUES('test@work.com'),
            ('test@work.test.com'))V(Email);