none
Domain Name from Email address

    Question

  • I want to extract just the domain value from the email address after @ and before .com . Keep in mind some case we might have values that  are .co.in

    currently i use function to extract the value which works great but not sure how to deal with values before .com

    SUBSTRING(email,CHARINDEX('@',email)+1,LEN(email)-CHARINDEX('@',email))
    example

    test@hotmail.com  the value expacted is "hotmaill"

    test@yahoo.co.in the value expected is "yahoo"

    Friday, March 02, 2012 11:53 PM

Answers

  • What about this one:

    Declare @myTable Table (Emails Varchar(100))
    Insert Into @myTable 
    Select 'ac@yahoo.com' Union All 
    Select 'ef@hotmail.co.a' Union All 
    Select 'eedfedf' Union All 
    Select 'aa.bb.cc@oi.com.oo'
    
    Select *
    	,(Case When CharIndex('@', Emails) <> 0 And CHARINDEX('.', SubString(Emails, CharIndex('@', Emails), LEN(Emails))) <> 0 Then SubString(Emails, CharIndex('@', Emails) + 1, CHARINDEX('.', SubString(Emails, CharIndex('@', Emails), LEN(Emails))) - 2) Else '' End) As DomainName 
    From @myTable 
    
    --output
    Emails	                DomainName
    ac@yahoo.com	        yahoo
    ef@hotmail.co.a	        hotmail
    eedfedf	
    aa.bb.cc@oi.com.oo	oi


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by ns100 Saturday, March 03, 2012 1:05 AM
    Saturday, March 03, 2012 12:43 AM

All replies

  • 
    
    Declare @myTable Table (Emails Varchar(100))
    Insert Into @myTable 
    Select 'ac@yahoo.com' Union All 
    Select 'ef@hotmail.co.a' Union All 
    Select 'eedfedf'
    
    Select *, (Case When CharIndex('@', Emails) <> 0  And CHARINDEX('.', Emails) <> 0 Then SUBSTRING(Emails, CharIndex('@', Emails) + 1, CHARINDEX('.', Emails) - CharIndex('@', Emails) - 1) Else '' End) As DomainName 
    From @myTable 
    
    --Output
    Emails	             DomainName
    ac@yahoo.com	     yahoo
    ef@hotmail.co.a	     hotmail
    eedfedf	



    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    Friday, March 02, 2012 11:57 PM
  • that works fine except that when you have '.' before @ symbol it fails

    nd.npp.oke@gmail.com

    Saturday, March 03, 2012 12:36 AM
  • What about this one:

    Declare @myTable Table (Emails Varchar(100))
    Insert Into @myTable 
    Select 'ac@yahoo.com' Union All 
    Select 'ef@hotmail.co.a' Union All 
    Select 'eedfedf' Union All 
    Select 'aa.bb.cc@oi.com.oo'
    
    Select *
    	,(Case When CharIndex('@', Emails) <> 0 And CHARINDEX('.', SubString(Emails, CharIndex('@', Emails), LEN(Emails))) <> 0 Then SubString(Emails, CharIndex('@', Emails) + 1, CHARINDEX('.', SubString(Emails, CharIndex('@', Emails), LEN(Emails))) - 2) Else '' End) As DomainName 
    From @myTable 
    
    --output
    Emails	                DomainName
    ac@yahoo.com	        yahoo
    ef@hotmail.co.a	        hotmail
    eedfedf	
    aa.bb.cc@oi.com.oo	oi


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by ns100 Saturday, March 03, 2012 1:05 AM
    Saturday, March 03, 2012 12:43 AM
  • The following article has an email parsing query using NULLIF:

    http://www.sqlusa.com/bestpractices2005/nullif/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    Saturday, March 03, 2012 12:54 AM