How to get the following result.Basically i am trying to get the domain name from the emailaddress.
expected result is:
Something like this should do it:
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 firstname.lastname@example.org 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.
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:
The last version is probably not going to be any faster than the second one, it just looks a little bit cleaner.
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
answered Dec 10 '10 at 01:46 PM