November 20, 2012 at 8:04 am
I have something out of order what is it I'm not sure, I want to
reverse the field(s) Doe.John@CompanyABC.com to
but my code: select SUBSTRING(Email, CHARINDEX('.', Email + 1, LEN(d Email)) + '.' + SUBSTRING(Email, -1, CHARINDEX('.', Email) +1)
from tbl$
gives me John@CompanyABC.com.Doe
November 20, 2012 at 8:19 am
Whatever you have posted cannot be compiled!
Here what you can do:
select SUBSTRING(Email, -1, CHARINDEX('.', Email) +1) +
SUBSTRING(Email, CHARINDEX('.', Email), LEN(Email))
from ...
November 20, 2012 at 8:35 am
the code you posted just give the lastname and company.com (doe@CompanyABC.com). I'm here Doe.John@CompanyABC.com And I want to get to John.Doe@CompanyABC.com
November 20, 2012 at 8:43 am
kd11 (11/20/2012)
the code you posted just give the lastname and company.com (doe@CompanyABC.com). I'm here Doe.John@CompanyABC.com And I want to get to John.Doe@CompanyABC.com
Not true. I've tested it:
declare @email varchar(100) = 'Doe.John@CompanyABC.com'
select SUBSTRING(@Email, -1, CHARINDEX('.', @Email) +1) +
SUBSTRING(@Email, CHARINDEX('.', @Email), LEN(@Email))
returns:
------------------------------------
(1 row(s) affected)
What are you using? SQL2008?
You may try another version:
declare @email varchar(100) = 'Doe.John@CompanyABC.com'
select SUBSTRING(@Email, 0, CHARINDEX('.', @Email)) +
SUBSTRING(@Email, CHARINDEX('.', @Email), LEN(@Email))
November 20, 2012 at 8:45 am
However, I've realised it doesn't change the name places...
November 20, 2012 at 8:52 am
declare @email varchar(100) = 'Doe.John@CompanyABC.com'
select SUBSTRING(@Email, CHARINDEX('.', @Email)+1,CHARINDEX('@', @Email)-CHARINDEX('.', @Email)-1)
+ '.'
+ SUBSTRING(@Email, 0, CHARINDEX('.', @Email))
+ SUBSTRING(@Email, CHARINDEX('@', @Email),LEN(@Email))
November 20, 2012 at 9:29 am
Another one from Lynn Pettis, for some reason he couldn't post it himself...
declare @email varchar(100) = 'Doe.John@CompanyABC.com'
SELECT
SUBSTRING(@email, 1, CHARINDEX('.', @email) - 1),
SUBSTRING(@email, CHARINDEX('.', @email) + 1, CHARINDEX('@', @email) - CHARINDEX('.', @email) - 1),
RIGHT(@email,LEN(@email) - CHARINDEX('@', @email) + 1),
SUBSTRING(@email, CHARINDEX('.', @email) + 1, CHARINDEX('@', @email) - CHARINDEX('.', @email) - 1) + '.' +
SUBSTRING(@email, 1, CHARINDEX('.', @email) - 1) +
RIGHT(@email,LEN(@email) - CHARINDEX('@', @email) + 1),
November 20, 2012 at 9:33 am
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
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2012 at 9:34 am
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...
November 20, 2012 at 9:55 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2012 at 10:05 am
ScottPletcher (11/20/2012)
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...
I don't think it was really due to use of CHARINDEX and SUBSTRING, it was more to do with REVERSE, which is not too fast one in T-SQL. However I can see the need for a dedicated test here...
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]
November 20, 2012 at 10:48 am
After a bit of testing, I can confirm that PARSENAME does win much more often over use of CHARINDEX with SUBSTRING's than other way around.
(Will keep this result in my head now, looks like I had a wrong assumption from somewhere...)
Mostly the difference is negligible, but still I would declare PARSENAME to be a winner in my little competition.
There is actually one way to increase performance slightly, using CROSS APPLY to calculates position of '@':
select @var = PARSENAME(left(email, AT - 1), 1) + '.' +
PARSENAME(left(email, AT - 1), 2) +
SUBSTRING(email, AT, len(email))
FROM #TestTable
CROSS APPLY (SELECT CHARINDEX('@', Email)) P(AT)
Now, I'm eager to create CLR one...;-)
November 20, 2012 at 10:50 am
November 20, 2012 at 12:17 pm
Eugene Elutin (11/20/2012)
After a bit of testing, I can confirm that PARSENAME does win much more often over use of CHARINDEX with SUBSTRING's than other way around.
(Will keep this result in my head now, looks like I had a wrong assumption from somewhere...)
Mostly the difference is negligible, but still I would declare PARSENAME to be a winner in my little competition.
Not to mention being vastly more readable and instantly understandable :-). It takes a while to determine what a long string of CHARINDEX/SUBSTRING/whatever is doing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2012 at 5:53 pm
Boys, boys. If you're going to argue about performance may I interject a new dog into this race?
DECLARE @Email VARCHAR(100) = 'John.Doe@companyabc.com'
SELECT Email=RIGHT(
STUFF(@Email
,CHARINDEX('@', @Email)
,1, '.' + LEFT(@Email, CHARINDEX('.', @Email)-1) + '@')
,LEN(@Email))
Here's a test harness (hope I selected the right code for you both):
DECLARE @Email VARCHAR(100)
CREATE TABLE #Emails (email VARCHAR(100))
;WITH Tally (n) AS (
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #Emails
SELECT 'doe' + CAST(n AS VARCHAR(7)) + '.John ' + CAST(n AS VARCHAR(7)) + '@CompanyABC.com'
FROM Tally
PRINT 'Eugene Eletin'
SET STATISTICS TIME ON
select @Email = SUBSTRING(email, CHARINDEX('.', email)+1,CHARINDEX('@', email)-CHARINDEX('.', email)-1)
+ '.'
+ SUBSTRING(email, 0, CHARINDEX('.', email))
+ SUBSTRING(email, CHARINDEX('@', email),LEN(email))
FROM #Emails
SET STATISTICS TIME OFF
PRINT 'Scott Pletcher'
SET STATISTICS TIME ON
select @Email=
PARSENAME(left(email, charindex('@', email) - 1), 1) + '.' +
PARSENAME(left(email, charindex('@', email) - 1), 2) +
substring(email, charindex('@', email), len(email))
FROM #Emails
SET STATISTICS TIME OFF
PRINT 'Dwain.C'
SET STATISTICS TIME ON
SELECT @Email=RIGHT(
STUFF(email
,CHARINDEX('@', email)
,1, '.' + LEFT(email, CHARINDEX('.', email)-1) + '@')
,LEN(email))
FROM #Emails
SET STATISTICS TIME OFF
DROP TABLE #Emails
And some results:
(1000000 row(s) affected)
Eugene Eletin
SQL Server Execution Times:
CPU time = 2294 ms, elapsed time = 2318 ms.
Scott Pletcher
SQL Server Execution Times:
CPU time = 2761 ms, elapsed time = 2810 ms.
Dwain.C
SQL Server Execution Times:
CPU time = 1295 ms, elapsed time = 1306 ms.
Woof!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply