Eugene Elutin (11/20/2012)
ScottPletcher (11/20/2012)
select PARSENAME(left(email, charindex('@', email) - 1), 1) + '.' + PARSENAME(left(email, charindex('@', email) - 1), 2) +
substring(email, charindex('@', email), len(email))
from (
select 'Doe.John@CompanyABC.com' as email
) as test_table
Just to note: PARSENAME is elegant but not the best performer...
Odd ... this article's testing showed the opposite:
http://www.sqlservercentral.com/articles/IPv4+Addresses/67534/
"
To eliminate deviations caused by table lookups, I looped each method 10,000 times on the same VARCHAR(15) IPv4 address. I performed the test 10 times to make sure the results were consistent.
SUBSTRING and CHARINDEX 3334 ms
PARSENAME ....................... 3325 ms
Improved SUBSTRING and CHARINDEX 3332 ms
Improved PARSENAME ........................ 3323 ms
It is very clear that there is little difference in performance between the different methods. However, it is also evident that PARSENAME is faster than multiple SUBSTRING and CHARINDEX calls.
"
[emphasis added]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.