June 24, 2012 at 9:16 pm
Experts,
I want to compare the text between 1st occurance of @ symbol and 1st occurance of any special character with 2nd occurance of @ symbol and next occurance of any special character. If both are not same, I would consider it as bad data.
Example:
I have employee email data in a column. My concern is only the domain part of emails.
Employee ID Email
------------ ----------
1 <mmooney@gmail.com>;"mmoney@gmail.com"
2 <chrisgardener@yahoo.com>)']chrisgardener@comcast.net)]
3 "natan@hotmail.com':>"natan@hotmail.com?'
4 "jennifer@walmart.com}';jennifer@fedex.com
I want to identify the 1st record and 3rd record as good data (because both domains are same); and 2nd record and 4th record as exceptions (because both domains are different)
What would be the best way to identify good ones and exceptions? Thanks in advance!
June 24, 2012 at 11:48 pm
This is not guaranteed to work in all cases (e.g., where @ may appear outside of an email string) but using Jeff Moden's DelimitedSplit8K string splitter, you can try something like this:
DECLARE @t TABLE (EmployeeID INT, Email VARCHAR(200))
INSERT INTO @t
SELECT 1, '<mmooney@gmail.com>;"mmoney@gmail.com"'
UNION ALL SELECT 2, '<chrisgardener@yahoo.com>)'']chrisgardener@comcast.net)]'
UNION ALL SELECT 3, '"natan@hotmail.com'':>"natan@hotmail.com?'''
UNION ALL SELECT 4, '"jennifer@walmart.com}'';jennifer@fedex.com'
SELECT EmployeeID, Email
FROM (
SELECT EmployeeID, Email, ItemNumber
,df=CASE WHEN PATINDEX('%[^0-9A-Za-z@.]%', Item) = 0
THEN Item
ELSE SUBSTRING(Item, 1, PATINDEX('%[^0-9A-Za-z@.]%', Item) - 1) END
FROM @t
CROSS APPLY dbo.DelimitedSplit8K(Email, '@') ds
WHERE CHARINDEX('.', Item) <> 0) x
GROUP BY EmployeeID, Email
HAVING MAX(df) = MIN(df)
The referenced string splitter can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
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
June 25, 2012 at 9:37 am
This works quite nicely too:
SELECT
EmployeeID,
Email,
DomainLHS = SUBSTRING(Email, x.PosStart,x.PosEnd-x.PosStart),
DomainRHS = SUBSTRING(Email, y.PosStart,y.PosEnd-y.PosStart)
FROM @t
CROSS APPLY (
SELECT PosStart = 1+CHARINDEX('@',EMAIL,1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,1)),0)
) x
CROSS APPLY (
SELECT PosStart = NULLIF(1+CHARINDEX('@',EMAIL,x.PosEnd),1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,x.PosEnd)),0)
) y
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2012 at 3:18 pm
Dwain, ChrisM@Work -
Both of your scripts are working great for my set of documents. Thanks much!
June 25, 2012 at 5:33 pm
dwain.c (6/24/2012)
This is not guaranteed to work in all cases (e.g., where @ may appear outside of an email string) but using Jeff Moden's DelimitedSplit8K string splitter, you can try something like this:
DECLARE @t TABLE (EmployeeID INT, Email VARCHAR(200))
INSERT INTO @t
SELECT 1, '<mmooney@gmail.com>;"mmoney@gmail.com"'
UNION ALL SELECT 2, '<chrisgardener@yahoo.com>)'']chrisgardener@comcast.net)]'
UNION ALL SELECT 3, '"natan@hotmail.com'':>"natan@hotmail.com?'''
UNION ALL SELECT 4, '"jennifer@walmart.com}'';jennifer@fedex.com'
SELECT EmployeeID, Email
FROM (
SELECT EmployeeID, Email, ItemNumber
,df=CASE WHEN PATINDEX('%[^0-9A-Za-z@.]%', Item) = 0
THEN Item
ELSE SUBSTRING(Item, 1, PATINDEX('%[^0-9A-Za-z@.]%', Item) - 1) END
FROM @t
CROSS APPLY dbo.DelimitedSplit8K(Email, '@') ds
WHERE CHARINDEX('.', Item) <> 0) x
GROUP BY EmployeeID, Email
HAVING MAX(df) = MIN(df)
The referenced string splitter can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Note that mine won't work in the case where a period (.) appears in the left part of the email name. If you were to provide additional test data, I think I could work around that. The original test data you gave us was pretty messy and I was afraid the real data would be even worse.
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
June 25, 2012 at 7:42 pm
ChrisM@Work (6/25/2012)
This works quite nicely too:
SELECTEmployeeID,
Email,
DomainLHS = SUBSTRING(Email, x.PosStart,x.PosEnd-x.PosStart),
DomainRHS = SUBSTRING(Email, y.PosStart,y.PosEnd-y.PosStart)
FROM @t
CROSS APPLY (
SELECT PosStart = 1+CHARINDEX('@',EMAIL,1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,1)),0)
) x
CROSS APPLY (
SELECT PosStart = NULLIF(1+CHARINDEX('@',EMAIL,x.PosEnd),1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,x.PosEnd)),0)
) y
Absolutely awesome, Chris. "Cascading Cross Applys" (cCA for short! :-D). Looks like it might beat the tar out of cascading CTE's of a similar nature. To be honest, I didn't know such a thing was possible because I didn't even consider that one CA might be able to use the output of another. This crazy Monday has turned into something good after all!
You should write a "Spackle" article on it, Chris.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2012 at 9:33 pm
dwain.c, Completely understood. I will update here of any exceptions and how I handle them. Thank you.
June 26, 2012 at 3:29 am
Jeff Moden (6/25/2012)
ChrisM@Work (6/25/2012)
This works quite nicely too:
SELECTEmployeeID,
Email,
DomainLHS = SUBSTRING(Email, x.PosStart,x.PosEnd-x.PosStart),
DomainRHS = SUBSTRING(Email, y.PosStart,y.PosEnd-y.PosStart)
FROM @t
CROSS APPLY (
SELECT PosStart = 1+CHARINDEX('@',EMAIL,1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,1)),0)
) x
CROSS APPLY (
SELECT PosStart = NULLIF(1+CHARINDEX('@',EMAIL,x.PosEnd),1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,x.PosEnd)),0)
) y
Absolutely awesome, Chris. "Cascading Cross Applys" (cCA for short! :-D). Looks like it might beat the tar out of cascading CTE's of a similar nature. To be honest, I didn't know such a thing was possible because I didn't even consider that one CA might be able to use the output of another. This crazy Monday has turned into something good after all!
You should write a "Spackle" article on it, Chris.
Gosh :blush: thanks Jeff!
I might have to do just that 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 8:13 am
ChrisM@Work (6/26/2012)
Jeff Moden (6/25/2012)
ChrisM@Work (6/25/2012)
This works quite nicely too:
SELECTEmployeeID,
Email,
DomainLHS = SUBSTRING(Email, x.PosStart,x.PosEnd-x.PosStart),
DomainRHS = SUBSTRING(Email, y.PosStart,y.PosEnd-y.PosStart)
FROM @t
CROSS APPLY (
SELECT PosStart = 1+CHARINDEX('@',EMAIL,1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,1)),0)
) x
CROSS APPLY (
SELECT PosStart = NULLIF(1+CHARINDEX('@',EMAIL,x.PosEnd),1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,x.PosEnd)),0)
) y
Absolutely awesome, Chris. "Cascading Cross Applys" (cCA for short! :-D). Looks like it might beat the tar out of cascading CTE's of a similar nature. To be honest, I didn't know such a thing was possible because I didn't even consider that one CA might be able to use the output of another. This crazy Monday has turned into something good after all!
You should write a "Spackle" article on it, Chris.
Gosh :blush: thanks Jeff!
I might have to do just that 😉
If you do that, I'd be honored to review it for you (Word 97-2003 if you don't mind) and then show you how to submit it through the "Write for us" link.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2012 at 6:04 am
Jeff Moden (6/26/2012)
ChrisM@Work (6/26/2012)
Jeff Moden (6/25/2012)
ChrisM@Work (6/25/2012)
This works quite nicely too:
SELECTEmployeeID,
Email,
DomainLHS = SUBSTRING(Email, x.PosStart,x.PosEnd-x.PosStart),
DomainRHS = SUBSTRING(Email, y.PosStart,y.PosEnd-y.PosStart)
FROM @t
CROSS APPLY (
SELECT PosStart = 1+CHARINDEX('@',EMAIL,1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,1)),0)
) x
CROSS APPLY (
SELECT PosStart = NULLIF(1+CHARINDEX('@',EMAIL,x.PosEnd),1),
PosEnd = NULLIF(CHARINDEX('.',EMAIL,CHARINDEX('@',EMAIL,x.PosEnd)),0)
) y
Absolutely awesome, Chris. "Cascading Cross Applys" (cCA for short! :-D). Looks like it might beat the tar out of cascading CTE's of a similar nature. To be honest, I didn't know such a thing was possible because I didn't even consider that one CA might be able to use the output of another. This crazy Monday has turned into something good after all!
You should write a "Spackle" article on it, Chris.
Gosh :blush: thanks Jeff!
I might have to do just that 😉
If you do that, I'd be honored to review it for you (Word 97-2003 if you don't mind) and then show you how to submit it through the "Write for us" link.
I'm speechless Jeff, that's very kind, thank you.
This technique isn't exactly new though. I can remember the first time I posted a solution using it, possibly as long as a couple of years ago. However, if you feel it's worthwhile (a few recent posts suggests it might be), then I'll go for it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 4, 2012 at 4:46 am
I just made the most awesome discovery while using cascading CROSS APPLYs so I thought I'd post it here.
Did you know that inside the CROSS APPLY if you refer to a name that's ambiguous in the left table, that the CROSS APPLY assumes that it is from the inner table? To use the column from the left table (if ambiguous) you must then qualify it with the table alias.
That is awesome cool and very handy to make the code concise!
All right, everybody already knew that so I'll shut up now. :blush: Just had to tell somebody.
Might be something worth mentioning in your article though Chris.
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
July 4, 2012 at 5:23 am
dwain.c (7/4/2012)
I just made the most awesome discovery while using cascading CROSS APPLYs so I thought I'd post it here.Did you know that inside the CROSS APPLY if you refer to a name that's ambiguous in the left table, that the CROSS APPLY assumes that it is from the inner table? To use the column from the left table (if ambiguous) you must then qualify it with the table alias.
That is awesome cool and very handy to make the code concise!
All right, everybody already knew that so I'll shut up now. :blush: Just had to tell somebody.
Might be something worth mentioning in your article though Chris.
Have you got an example, Dwain?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 4, 2012 at 9:10 am
ChrisM@Work (7/4/2012)
dwain.c (7/4/2012)
I just made the most awesome discovery while using cascading CROSS APPLYs so I thought I'd post it here.Did you know that inside the CROSS APPLY if you refer to a name that's ambiguous in the left table, that the CROSS APPLY assumes that it is from the inner table? To use the column from the left table (if ambiguous) you must then qualify it with the table alias.
That is awesome cool and very handy to make the code concise!
All right, everybody already knew that so I'll shut up now. :blush: Just had to tell somebody.
Might be something worth mentioning in your article though Chris.
Have you got an example, Dwain?
I think Dwain might be referrring to the issue raised in these links:
http://www.sqlservercentral.com/Forums/Topic890645-338-1.aspx#bm891174
http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2010/03/26/cross-apply-ambiguity/
July 4, 2012 at 10:04 am
ChrisM@Work (6/27/2012)
This technique isn't exactly new though. I can remember the first time I posted a solution using it, possibly as long as a couple of years ago. However, if you feel it's worthwhile (a few recent posts suggests it might be), then I'll go for it.
Consider what happened with the Tally Table. It was actually a technique that some folks used on main frames way back when. Since then, it's been written about dozens if not hundreds of times and who knows how many thousands of posts there were on the subject before I wrote about it. I wrote about it because, despite the number of times it had been written about and posted, people weren't getting how it worked.
Now, consider that I hadn't even considered using the output of one CROSS APPLY in another in the same query. How many other folks may be missing that epiphany?
Start the introduction of the article with something like "Yeah... I know this is an old trick but, judging from the number of posts that could actually benefit from it, I thought I'd write about it so that even a Neophyte to SQL can understand it well enough to take advantage of the power and simplicity-of-code the technique offers."
It would make a great "Spackle" article.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 4, 2012 at 8:17 pm
ChrisM@Work (7/4/2012)
dwain.c (7/4/2012)
I just made the most awesome discovery while using cascading CROSS APPLYs so I thought I'd post it here.Did you know that inside the CROSS APPLY if you refer to a name that's ambiguous in the left table, that the CROSS APPLY assumes that it is from the inner table? To use the column from the left table (if ambiguous) you must then qualify it with the table alias.
That is awesome cool and very handy to make the code concise!
All right, everybody already knew that so I'll shut up now. :blush: Just had to tell somebody.
Might be something worth mentioning in your article though Chris.
Have you got an example, Dwain?
Indeed I do! Take a look at the code I posted here: http://www.sqlservercentral.com/Forums/Topic1318149-392-10.aspx?Update=1, specifically the code I referred to as the "mother of all cascaded CROSS APPLYs" (last SQL script).
A little explanation is in order I suppose. Stealing a chunk of that code (that won't run on its own):
FROM BaseDistricts base
-- Try: [6,55] Fail (no rows returned)
-- Try: [8,25] Success (at least one row returned)
CROSS APPLY (
SELECT d13=d1, d14=d2, dall, [population]
FROM dbo.NewDistricts
WHERE n = 2 AND d1 = 8 AND d2 = 25 AND
d1 NOT IN (base.d1,base.d2,base.d3,d4,d5,d6,d7,d8,d9,d10,d11,d12) AND
d2 NOT IN (base.d1,base.d2,base.d3,d4,d5,d6,d7,d8,d9,d10,d11,d12)) a
-- Try: [8,66] Fail
-- Try: [25,55] Fail
-- Try: [36,40] Success!
CROSS APPLY (
SELECT d15=d1, d16=d2, dall, [population]
FROM dbo.NewDistricts
WHERE n = 2 AND d1 = 36 AND d2 = 40 AND
d1 NOT IN (base.d1,base.d2,base.d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14) AND
d2 NOT IN (base.d1,base.d2,base.d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14)) b
1. BaseDistricts is my base table and it contains the columns d1, d2, ... d12.
2. Derived table a references all 12 of these fields, however only d1 and d2 need to be qualified as base. I also qualified d3 for purposes down the line.
3. Derived table a creates a new d1 and d2 which can be referred to locally and are known as such without qualification. You only need to qualify d1 when you want to refer to the version created by base.
4. Derived table b now references d13 and d14, which were created in derived table a also without qualification. Once again, non-qualified references to d1 and d2 refer to the d1 and d2 that come out of the NewDistricts table.
Perhaps this should be known as a cascading, correlated CROSS APPLY!
Hope that helps!
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply