How to part of string?

Something like this should do it:

select
    substring(EmailAddress, charindex('@', EmailAddress) + 1, 
    charindex('.', EmailAddress) - charindex('@', EmailAddress) - 1) 
    from @EmailAddress;

The idea is to select a part of the string beginning from the character after the @ and pick the number of characters equal to the position of @ minus potition of the dot minus 1.

To be on the safe side, it would be better to handle the situation when the name part of email contains dots, which is possible. For example, if you have an entry bozo.theclown@circus.net then this entry will break the original query I posted, so it would be better to enhance it a bit by limiting the search for a dot position to begin after the @ has already been found, i.e.

select
    substring(EmailAddress, charindex('@', EmailAddress) + 1, 
    charindex('.', EmailAddress, charindex('@', EmailAddress)) - 
    charindex('@', EmailAddress) - 1) 
    from @EmailAddress;

Onle last touchup: the charindex('@', EmailAddress) is repeated 3 times, so it might be worth it to consider outsourcing the activity to the CTE, so the script looks a bit cleaner:

;with records(EmailAddress, position) as
(
    select 
        EmailAddress, charindex('@', EmailAddress) position
        from @EmailAddress
)
    select 
        substring(EmailAddress, position + 1, 
        charindex('.', EmailAddress, position) - position - 1)
        from records;

The last version is probably not going to be any faster than the second one, it just looks a little bit cleaner.

Oleg

Oleg's answer is fine. Here's just a little variation I tried out of curiosity. I gave it a few tweaks to cope with anomalous (incorrect) addresses

DECLARE @EmailAddress TABLE (EmailAddress NVARCHAR(50),
                             Ampersandlocation AS CHARINDEX('@', EmailAddress),
                             firstdotAfterAmpersandLocation INT)
INSERT  INTO @EmailAddress (EmailAddress)
        SELECT  'ABC@gmail.com'
        UNION ALL
        SELECT  'XYZ@yahoo.com'
        UNION ALL
        SELECT  'lmn@yahoo.co.in'
        UNION ALL
        SELECT  'Phil.Factor@Simple-Talk.com'
        UNION ALL
        SELECT  'PhilFactorBadAddresscom'
        UNION ALL
        SELECT  'PhilFactor@BadAddresscom'


SELECT  SUBSTRING(EmailAddress, Ampersandlocation + 1,
                  firstdotAfterAmpersandLocation - Ampersandlocation - 1)
FROM (SELECT CHARINDEX('.', EmailAddress + '.', Ampersandlocation)
                                        AS firstdotAfterAmpersandLocation,
             AmpersandLocation,EmailAddress
      FROM    @EmailAddress )f