x

How to part of string?

Hi,

How to get the following result.Basically i am trying to get the domain name from the emailaddress.

DECLARE @EmailAddress TABLE (EmailAddress NVARCHAR(50))
INSERT INTO @EmailAddress 
VALUES ('ABC@gmail.com','XYZ@yahoo.com','lmn@yahoo.co.in')
expected result is:
gmail
yahoo
yahoo
more ▼

asked Dec 09, 2010 at 11:58 AM in Default

swethaashwini gravatar image

swethaashwini
223 17 18 20

@swethaashwini: if @Oleg answer was helpful and solves your problem please mark it as answer so that others will find the solution quickly int he future.
Dec 09, 2010 at 12:42 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Dec 09, 2010 at 12:05 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Thanks Oleg. It works perfect. But what if i have an email address like 'ab.c@gmail.com'. How does we take care of this situation too?
Dec 09, 2010 at 12:15 PM swethaashwini
@swetha There are 2 scripts in my answers, the second one addresses the issue. I typed my answer too fast, this is why the original query did not account for this scenario.
Dec 09, 2010 at 12:19 PM Oleg
Thanks Oleg, this is what i was exactly looking for.
Dec 09, 2010 at 12:28 PM swethaashwini
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Dec 10, 2010 at 01:46 PM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x7
x5

asked: Dec 09, 2010 at 11:58 AM

Seen: 3010 times

Last Updated: Dec 09, 2010 at 12:22 PM