How to part of string?


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:

more ▼

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

avatar image

223 18 18 23

@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

2 answers: sort voted first

Something like this should do it:

     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.

     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
         EmailAddress, charindex('@', EmailAddress) position
         from @EmailAddress
         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.


more ▼

answered Dec 09, 2010 at 12:05 PM

avatar image

20.6k 3 7 29

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,
       FROM    @EmailAddress )f
more ▼

answered Dec 10, 2010 at 01:46 PM

avatar image

Phil Factor
4.2k 8 27 21

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Dec 09, 2010 at 11:58 AM

Seen: 3741 times

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

Copyright 2018 Redgate Software. Privacy Policy