January 2, 2013 at 6:19 pm
I need to get the emails from the n.Note field, which is a nVarchar(max) column.
I have the following, but I get an error saying "Invalid length parameter passed to the LEFT or SUBSTRING Function.
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;
January 2, 2013 at 6:37 pm
It looks like you have some data that does not meet the requirements (an invalid email address or the @ symbol is present without an email address).
Can you provide sample data for this field?
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 2, 2013 at 10:41 pm
tbmabry (1/2/2013)
I need to get the emails from the n.Note field, which is a nVarchar(max) column.I have the following, but I get an error saying "Invalid length parameter passed to the LEFT or SUBSTRING Function.
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;
Split each of the function in a separate query and run, then you know which function is not working. Alternately filter the records by customer number till you get error batch then check the data for that batch.
January 2, 2013 at 11:07 pm
Instead of write-only code, I've always found it immensely helpful to split up complex string manipulations for readability into individual parts using cascading CROSS APPLYs like this:
;WITH Notes (Note) AS (
SELECT 'aaa abc@wondwerks.com bbb'
UNION ALL SELECT 'dwain.c@sqlservercentral.com'
UNION ALL SELECT 'dwain.c@'
UNION ALL SELECT '@sqlservercentral.com'
UNION ALL SELECT 'aaa @sqlservercentral-com'
UNION ALL SELECT '@')
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)
WHERE LEFT(email, 1) <> '@' AND RIGHT(email, 1) <> '@'
If you can apply this to your Notes field, you should be able to find only email addresses that contain text on both sides of the @.
Alternatively, you can also use a PATINDEX to isolate only the valid email addresses like this:
;WITH Notes (Note) AS (
SELECT 'aaa abc@wondwerks.com bbb'
UNION ALL SELECT 'dwain.c@sqlservercentral.com'
UNION ALL SELECT 'dwain.c@'
UNION ALL SELECT '@sqlservercentral.com'
UNION ALL SELECT 'aaa @sqlservercentral-com'
UNION ALL SELECT '@')
SELECT Note
FROM Notes
WHERE Note LIKE '%[0-9A-Za-z.][@][0-9A-Za-z.]%'
You'll probably need to add in a few characters to the left [0-9A-Za-z.], like underscore (_), etc. to include all possible valid email address characters.
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 2, 2013 at 11:14 pm
I tried this but it didn't work. I don't know what the emails are going to be they are from all over the U.S. There are over 4000 records that I am going to be pulling from.
January 2, 2013 at 11:22 pm
tbmabry (1/2/2013)
I tried this but it didn't work. I don't know what the emails are going to be they are from all over the U.S. There are over 4000 records that I am going to be pulling from.
Since you didn't use the Quote button, it is difficult to tell whose suggestion you tried.
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 2, 2013 at 11:39 pm
tbmabry (1/2/2013)
I tried this but it didn't work. I don't know what the emails are going to be they are from all over the U.S. There are over 4000 records that I am going to be pulling from.
have you written the below code yourself
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)
IF yes then first only select the innermost function then start overlapping the outer ones .one by one.
this can help you to trace the culprit record.
another thing , first select TOP 100 then TOP 500 and so on , then see which selection is giving error. in this way you can avoid to traverse complete 4000 records.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 2, 2013 at 11:45 pm
Bhuvnesh (1/2/2013)
tbmabry (1/2/2013)
I tried this but it didn't work. I don't know what the emails are going to be they are from all over the U.S. There are over 4000 records that I am going to be pulling from.have you written the below code yourself
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)IF yes then first only select the innermost function then start overlapping the outer ones .one by one.
this can help you to trace the culprit record.
another thing , first select TOP 100 then TOP 500 and so on , then see which selection is giving error. in this way you can avoid to traverse complete 4000 records.
http://rjlipton.wordpress.com/2012/04/04/a-lazy-approach-to-problem-solving/
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 8:55 am
Tbmabry, did this post by dwain help you?
dwain.c (1/2/2013)
Instead of write-only code, I've always found it immensely helpful to split up complex string manipulations for readability into individual parts using cascading CROSS APPLYs like this:
;WITH Notes (Note) AS (
SELECT 'aaa abc@wondwerks.com bbb'
UNION ALL SELECT 'dwain.c@sqlservercentral.com'
UNION ALL SELECT 'dwain.c@'
UNION ALL SELECT '@sqlservercentral.com'
UNION ALL SELECT 'aaa @sqlservercentral-com'
UNION ALL SELECT '@')
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)
WHERE LEFT(email, 1) <> '@' AND RIGHT(email, 1) <> '@'
If you can apply this to your Notes field, you should be able to find only email addresses that contain text on both sides of the @.
Alternatively, you can also use a PATINDEX to isolate only the valid email addresses like this:
;WITH Notes (Note) AS (
SELECT 'aaa abc@wondwerks.com bbb'
UNION ALL SELECT 'dwain.c@sqlservercentral.com'
UNION ALL SELECT 'dwain.c@'
UNION ALL SELECT '@sqlservercentral.com'
UNION ALL SELECT 'aaa @sqlservercentral-com'
UNION ALL SELECT '@')
SELECT Note
FROM Notes
WHERE Note LIKE '%[0-9A-Za-z.][@][0-9A-Za-z.]%'
You'll probably need to add in a few characters to the left [0-9A-Za-z.], like underscore (_), etc. to include all possible valid email address characters.
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 9:00 pm
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;
January 3, 2013 at 9:36 pm
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.
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 9:38 pm
Try 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 '%@%'
)
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;
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 9:41 pm
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. 😎
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 9:50 pm
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.
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 9:55 pm
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.
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
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply