question

swethaashwini avatar image
swethaashwini asked

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
t-sqlcharindexstring-function
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image DaniSQL commented ·
@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.
0 Likes 0 ·
Oleg avatar image
Oleg answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

swethaashwini avatar image swethaashwini commented ·
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?
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@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.
0 Likes 0 ·
swethaashwini avatar image swethaashwini commented ·
Thanks Oleg, this is what i was exactly looking for.
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.