January 3, 2013 at 10:07 pm
SQLRNNR (1/3/2013)
dwain.c (1/3/2013)
SQLRNNR (1/3/2013)
dwain.c (1/3/2013)
tbmabry (1/3/2013)
Ok, thanks. I found one of them that is making it blow up. It is that the note only has earl@dcande.com with nothing else so no space before the earl part. How do I get around this. I am using the ...Select s.FirstName, s.LastName, c.CustomerNo, h.OppNo, n.Note,
SUBSTRING(SUBSTRING(n.Note,1,CHARINDEX(' ',n.Note,CHARINDEX('@', n.Note))-1),CHARINDEX('@', n.Note) - CHARINDEX(' ',REVERSE(SUBSTRING(n.Note,1,CHARINDEX('@', n.Note)-1))) + 1, 255)
from SalesLink_SalesRep s,
SalesLink_Prospect_SalesRepCustomerXrf c,
Opportunity_Header h,
Opportunity_Note n
where c.SalesRepId = s.SalesRepId
and c.CustomerNo = h.CustomerNo
and h.OppNo = n.OppNo
and s.SalesRepId in (238, 328)
and n.Note like '%@%'
order by CustomerNo;
Instead of trying to fix the SUBSTRINGing you've done, why not just try my version (#1). It handles that case quite nicely.
That would be a good idea. 😎
What nobody pointed out but should have, was that your first answer nailed the problem immediately.
I'm not sure if anybody looked at that link I posted about lazy problem solving. My solution gave two alternatives (both lazy):
1. Throw away the original query and start fresh with one that works.
2. Avoid the problem by removing problematic emails.
Too bad no one got it.
Nevermind the problem with the leads and contacts system (it is a common practice from what I have seen) that allows entry of email addresses to be put into the notes and then the email address field is not updated. Thus causing the note field to be the the only place where the reliable data is found - but is not stored in any standard format.
Enforce rules on the application that requires an email address be input into a field that accepts only email addresses. Same goes for phone numbers and addresses. Save the DBA a headache down the road.
:w00t: Ain't it amazing what people will do? Give 'em a remark field and they think they can type any old thing into it. :hehe:
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
January 3, 2013 at 10:33 pm
Okay everyone, you say to do #1 solution I am confused on which one you are talking about. I have just tried this one...
WITH presel AS (
SELECT s.FirstName, s.LastName, c.CustomerNo, h.OppNo, ' ' + n.Note AS Note
FROM SalesLink_SalesRep s
INNER JOIN SalesLink_Prospect_SalesRepCustomerXrf c
ON c.SalesRepId = s.SalesRepId
INNER JOIN Opportunity_Header h
ON c.CustomerNo = h.CustomerNo
INNER JOIN Opportunity_Note n
ON h.OppNo = n.OppNo
WHERE s.SalesRepId in (238, 328)
AND n.Note like '%@%'
)
SELECT Firstname,LastName,CustomerNo,OppNo,Note
,SUBSTRING(
SUBSTRING(Note,1,
CHARINDEX(' ',Note,CHARINDEX('@', Note))-1),
CHARINDEX('@', Note) - CHARINDEX(' ',REVERSE(SUBSTRING(Note,1,CHARINDEX('@', Note)-1))) + 1, 255)
FROM presel
ORDER BY CustomerNo;
I still get the Invalid length error.... Which one are you guys saying should work?
January 3, 2013 at 10:34 pm
The script referred to as option #1 is the first script in Dwain's first response on this thread.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 3, 2013 at 10:47 pm
What you posted doesn't look like mine, but based on the query you just posted, it should end up looking something like this:
WITH presel AS (
SELECT s.FirstName, s.LastName, c.CustomerNo, h.OppNo, ' ' + n.Note AS Note
FROM SalesLink_SalesRep s
INNER JOIN SalesLink_Prospect_SalesRepCustomerXrf c
ON c.SalesRepId = s.SalesRepId
INNER JOIN Opportunity_Header h
ON c.CustomerNo = h.CustomerNo
INNER JOIN Opportunity_Note n
ON h.OppNo = n.OppNo
WHERE s.SalesRepId in (238, 328)
AND n.Note LIKE '%[0-9A-Za-z.][@][0-9A-Za-z.]%'
)
SELECT Firstname,LastName,CustomerNo,OppNo,Note
FROM presel
CROSS APPLY (SELECT CHARINDEX('@', Note)) a (PosOfAt)
CROSS APPLY (
SELECT SUBSTRING(Note, 1, PosOfAt - 1)
,SUBSTRING(Note, PosOfAt + 1, LEN(Note))) b(Leading, Trailing)
CROSS APPLY (
SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +
LEFT(Trailing, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)
ORDER BY CustomerNo;
Note that I used the PATINDEX LIKE clause in your CTE to eliminate any invalid email addresses (my option #2).
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
January 3, 2013 at 10:50 pm
ok but that is just making them not be able to put the email in the notes this way. I can't change that. This is another company that does it and we are getting their data. Is there a way to code something to go through the notes and get the emails? But like I shared before I have notes like the following...
note 1: This is a note with email within tomme@gmail.com this ending the note.
note 2: stacey@comcast.net at the beginning with no spaces before or after.
note 3: timmy@hotmail.com
note 4: this is the email when address is at the end kimm@uw.net
all different emails and all different ways.
Any suggestions? I have tried using the substring in the select and then I have also tried creating a temp table and pulling the email with substring from the temp table.
January 3, 2013 at 10:58 pm
Works for all those cases:
;WITH Notes (Note) AS (
SELECT 'This is a note with email within tomme@gmail.com this ending the note.'
UNION ALL SELECT 'stacey@comcast.net at the beginning with no spaces before or after.'
UNION ALL SELECT 'timmy@hotmail.com'
UNION ALL SELECT 'this is the email when address is at the end kimm@uw.net'
)
SELECT email
FROM Notes
CROSS APPLY (SELECT CHARINDEX('@', Note)) a (PosOfAt)
CROSS APPLY (
SELECT SUBSTRING(Note, 1, PosOfAt - 1)
,SUBSTRING(Note, PosOfAt + 1, LEN(Note))) b(Leading, Trailing)
CROSS APPLY (
SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +
LEFT(Trailing, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)
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
January 3, 2013 at 11:23 pm
I have the following Microsoft SQL but it doesn't know what c(email) is and if I take it out I get no results. 🙁
--Get all the notes from the customers for 238 and 328 that include emails
with prese1 as(
Select top 8 s.SalesRepId, s.FirstName, s.LastName, c.CustomerNo, h.OppNo, ' ' + n.Note AS Note
from SalesLink_SalesRep s,
SalesLink_Prospect_SalesRepCustomerXrf c,
Opportunity_Header h,
Opportunity_Note n
CROSS APPLY (Select CHARINDEX('@', Note)) a (PosOfAt)
CROSS APPLY (SELECT SUBSTRING(Note, 1, PosOfAt -1)
,SUBSTRING(Note, PosOfAt - 1, LEN(Note))) b(Leading, Trailing)
CROSS APPLY (SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +
LEFT(TRAILING, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)
where c.SalesRepId = s.SalesRepId
and c.CustomerNo = h.CustomerNo
and h.OppNo = n.OppNo
and s.SalesRepId in (238, 328)
and n.Note like '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
order by CustomerNo)
select FirstName, LastName, CustomerNo, OppNo, Note, SUBSTRING(SUBSTRING(Note, 1, CHARINDEX(' ', Note, CHARINDEX('@', Note)) - 1), CHARINDEX('@', Note) - CHARINDEX(' ', REVERSE(SUBSTRING(Note, 1, CHARINDEX('@', Note)-1))) + 1, 255)
from prese1
order by CustomerNo;
January 3, 2013 at 11:28 pm
tbmabry (1/3/2013)
I have the following Microsoft SQL but it doesn't know what c(email) is and if I take it out I get no results. 🙁--Get all the notes from the customers for 238 and 328 that include emails
with prese1 as(
Select top 8 s.SalesRepId, s.FirstName, s.LastName, c.CustomerNo, h.OppNo, ' ' + n.Note AS Note
from SalesLink_SalesRep s,
SalesLink_Prospect_SalesRepCustomerXrf c,
Opportunity_Header h,
Opportunity_Note n
CROSS APPLY (Select CHARINDEX('@', Note)) a (PosOfAt)
CROSS APPLY (SELECT SUBSTRING(Note, 1, PosOfAt -1)
,SUBSTRING(Note, PosOfAt - 1, LEN(Note))) b(Leading, Trailing)
CROSS APPLY (SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +
LEFT(TRAILING, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)
where c.SalesRepId = s.SalesRepId
and c.CustomerNo = h.CustomerNo
and h.OppNo = n.OppNo
and s.SalesRepId in (238, 328)
and n.Note like '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
order by CustomerNo)
select FirstName, LastName, CustomerNo, OppNo, Note, SUBSTRING(SUBSTRING(Note, 1, CHARINDEX(' ', Note, CHARINDEX('@', Note)) - 1), CHARINDEX('@', Note) - CHARINDEX(' ', REVERSE(SUBSTRING(Note, 1, CHARINDEX('@', Note)-1))) + 1, 255)
from prese1
order by CustomerNo;
You realize that I'm right on the verge of giving up here, right?
Did you see the below post? Use exactly that query and not the one above!
dwain.c (1/3/2013)
What you posted doesn't look like mine, but based on the query you just posted, it should end up looking something like this:
WITH presel AS (
SELECT s.FirstName, s.LastName, c.CustomerNo, h.OppNo, ' ' + n.Note AS Note
FROM SalesLink_SalesRep s
INNER JOIN SalesLink_Prospect_SalesRepCustomerXrf c
ON c.SalesRepId = s.SalesRepId
INNER JOIN Opportunity_Header h
ON c.CustomerNo = h.CustomerNo
INNER JOIN Opportunity_Note n
ON h.OppNo = n.OppNo
WHERE s.SalesRepId in (238, 328)
AND n.Note LIKE '%[0-9A-Za-z.][@][0-9A-Za-z.]%'
)
SELECT Firstname,LastName,CustomerNo,OppNo,Note
FROM presel
CROSS APPLY (SELECT CHARINDEX('@', Note)) a (PosOfAt)
CROSS APPLY (
SELECT SUBSTRING(Note, 1, PosOfAt - 1)
,SUBSTRING(Note, PosOfAt + 1, LEN(Note))) b(Leading, Trailing)
CROSS APPLY (
SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +
LEFT(Trailing, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)
ORDER BY CustomerNo;
Note that I used the PATINDEX LIKE clause in your CTE to eliminate any invalid email addresses (my option #2).
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
January 3, 2013 at 11:56 pm
Ok, I know you are sick of me. I know I am just going crazy over this. I have to get this done and can't figure this out...I can't put the INNER JOiN etc it doesn't like that. But what I have below is running with no errors but doesn't bring back anything. The temp table doesn't have anything in it.
--Get all the notes from the customers for 238 and 328 that include emails
with prese1 as(
Select top 8 s.SalesRepId, s.FirstName, s.LastName, c.CustomerNo, h.OppNo, ' ' + n.Note AS Note
from SalesLink_SalesRep s,
SalesLink_Prospect_SalesRepCustomerXrf c,
Opportunity_Header h,
Opportunity_Note n
where c.SalesRepId = s.SalesRepId
and c.CustomerNo = h.CustomerNo
and h.OppNo = n.OppNo
and s.SalesRepId in (238, 328)
and n.Note like '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
order by CustomerNo)
select FirstName, LastName, CustomerNo, OppNo, Note, email
from prese1
CROSS APPLY (Select CHARINDEX('@', Note)) a (PosOfAt)
CROSS APPLY (SELECT SUBSTRING(Note, 1, PosOfAt -1)
,SUBSTRING(Note, PosOfAt - 1, LEN(Note))) b(Leading, Trailing)
CROSS APPLY (SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +
LEFT(TRAILING, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)
order by CustomerNo;
January 4, 2013 at 12:01 am
tbmabry (1/3/2013)
Ok, I know you are sick of me. I know I am just going crazy over this. I have to get this done and can't figure this out...I can't put the INNER JOiN etc it doesn't like that. But what I have below is running with no errors but doesn't bring back anything. The temp table doesn't have anything in it.--Get all the notes from the customers for 238 and 328 that include emails
with prese1 as(
Select top 8 s.SalesRepId, s.FirstName, s.LastName, c.CustomerNo, h.OppNo, ' ' + n.Note AS Note
from SalesLink_SalesRep s,
SalesLink_Prospect_SalesRepCustomerXrf c,
Opportunity_Header h,
Opportunity_Note n
where c.SalesRepId = s.SalesRepId
and c.CustomerNo = h.CustomerNo
and h.OppNo = n.OppNo
and s.SalesRepId in (238, 328)
and n.Note like '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
order by CustomerNo)
select FirstName, LastName, CustomerNo, OppNo, Note, email
from prese1
CROSS APPLY (Select CHARINDEX('@', Note)) a (PosOfAt)
CROSS APPLY (SELECT SUBSTRING(Note, 1, PosOfAt -1)
,SUBSTRING(Note, PosOfAt - 1, LEN(Note))) b(Leading, Trailing)
CROSS APPLY (SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +
LEFT(TRAILING, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)
order by CustomerNo;
What about the inner join does it not like? Are you getting an error?
Have you verified that salesrepid 238 and 328 exist? How about the rest of the criteria in the where clause - did you make sure that the data meets all of those criteria?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2013 at 12:04 am
Not sick of you. Just not understanding why this isn't working.
You've changed the INNER JOINs to a CROSS JOIN, which I wouldn't necessarily expect to return the same result, although an empty set is unexpected.
What exactly happens when you run with the INNER JOINs? In other words, what error message tells you that SQL doesn't like that?
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
January 4, 2013 at 12:14 am
Yes there is data for 238 for top 8. I run just pulling the notes and it works. but as soon as I add the '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
It doesn't put any data into the temp table. I don't get any errors or anything. Says it ran successful, just doesn't return anything.
with prese1 as(
Select top 8 s.SalesRepId, s.FirstName, s.LastName, c.CustomerNo, h.OppNo, ' ' + n.Note AS Note
from SalesLink_SalesRep s,
SalesLink_Prospect_SalesRepCustomerXrf c,
Opportunity_Header h,
Opportunity_Note n
where c.SalesRepId = s.SalesRepId
and c.CustomerNo = h.CustomerNo
and h.OppNo = n.OppNo
and s.SalesRepId in (238, 328)
and n.Note like '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
order by CustomerNo)
select FirstName, LastName, CustomerNo, OppNo, Note, email
from prese1
CROSS APPLY (Select CHARINDEX('@', Note)) a (PosOfAt)
CROSS APPLY (SELECT SUBSTRING(Note, 1, PosOfAt -1)
,SUBSTRING(Note, PosOfAt - 1, LEN(Note))) b(Leading, Trailing)
CROSS APPLY (SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +
LEFT(TRAILING, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)
order by CustomerNo;
January 4, 2013 at 12:17 am
tbmabry (1/4/2013)
Yes there is data for 238 for top 8. I run just pulling the notes and it works. but as soon as I add the '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'It doesn't put any data into the temp table. I don't get any errors or anything. Says it ran successful, just doesn't return anything.
with prese1 as(
Select top 8 s.SalesRepId, s.FirstName, s.LastName, c.CustomerNo, h.OppNo, ' ' + n.Note AS Note
from SalesLink_SalesRep s,
SalesLink_Prospect_SalesRepCustomerXrf c,
Opportunity_Header h,
Opportunity_Note n
where c.SalesRepId = s.SalesRepId
and c.CustomerNo = h.CustomerNo
and h.OppNo = n.OppNo
and s.SalesRepId in (238, 328)
and n.Note like '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
order by CustomerNo)
select FirstName, LastName, CustomerNo, OppNo, Note, email
from prese1
CROSS APPLY (Select CHARINDEX('@', Note)) a (PosOfAt)
CROSS APPLY (SELECT SUBSTRING(Note, 1, PosOfAt -1)
,SUBSTRING(Note, PosOfAt - 1, LEN(Note))) b(Leading, Trailing)
CROSS APPLY (SELECT REVERSE(LEFT(REVERSE(Leading), CHARINDEX(' ', REVERSE(Leading) + ' ') - 1)) + '@' +
LEFT(TRAILING, CHARINDEX(' ', Trailing + ' ') - 1)) c(email)
order by CustomerNo;
What happens when you comment out this line?
-- and n.Note like '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
Also try removing the space between @] and [0-9
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
January 4, 2013 at 12:17 am
And if you remove the top 8?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2013 at 12:20 am
You should recheck my last post as I edited it.
You've got a space in the pattern string that could be impacting it.
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 - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply