January 4, 2013 at 12:27 am
If I run this I get data back.....
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)
order by CustomerNo)
select FirstName, LastName, CustomerNo, OppNo, Note
from prese1
If I run this I don't get any data
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
from prese1
January 4, 2013 at 12:30 am
Like Dwain said,
change this line
and n.Note like '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
to this
and n.Note like '%[0-9A-Za-z.][@][0-9A-Za-z.]%'
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 1:42 am
SQLRNNR (1/4/2013)
Like Dwain said,change this line
and n.Note like '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
to this
and n.Note like '%[0-9A-Za-z.][@][0-9A-Za-z.]%'
That space is definitely the problem because when you run the query below, it also returns no rows. Remove the space and all 4 rows are returned.
;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)
WHERE Note LIKE '%[0-9A-Za-z.][@] [0-9A-Za-z.]%'
Still curious what was happening that made the OP think that SQL didn't like the INNER JOIN version of his query though.
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 10:50 am
Thanks everyone. Works great.
January 4, 2013 at 10:57 am
Glad it is working for you.
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 2:36 pm
I lied. I found a problem. I am reading right now about cross apply. Right now my email's are adding in an extra letter and and an extra @ sign. So I have emails but they have two @ signs. For examaple
svanderwaal@rainbow.ca is showing as 'svanderwaal@l@rainbow.ca'
m.crolla@sky.com is showing as 'm.crolla@a@sky.com Preferred' (so bringin in more than email.)
housing_project@163.com is showing 'housing_project@t@163.com Telephone:' (so bringin in more than email.)
This is my code again I am using
--Get all the notes from the customers for 238 and 328 that include emails
with prese1 as(
Select 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.]%')
select FirstName, LastName, CustomerNo, OppNo, 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 2:40 pm
Please provide sample data so a better solution can be provided.
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 3:04 pm
example 1 (not all have email: before the email that is why I look for the @ sign.
FirstName LastName CustomerN OppNo note
KeithZwar$000879 102444 -------------------------------------------------------- Name: Stan Vander Waal Title: Partner/Principal Company: Rainbow Greenhouses Inc Company Size: 100-499 Industry Type: Agriculture Address: 2026 Timon Road Location: Everson, Washington 98247* Phone number: 604-858-8100 Email: svanderwaal@rainbow.ca *Please note: This address may be different than the address in which the buyer is looking to purchase a product or service. We match leads to suppliers based on the zip code in the Request for Quote below. ========================================================== REQUEST FOR QUOTE ---------------------------------------------------------- Number of wheel loaders needed: 1 If more than 4+, exact number required: Condition of wheel loader preferred: Not sure Application for wheel loader: Agriculture Size required: 100 - 175 hp Preferred finance method: Purchase Buying timeframe: ASAP User's office location: 98247 Additional requirements: ========================================================== Questions? E-mail custcare@buyerzone.com or call (888)393-5000.
WHICH should bring back email = svanderwaal@rainbow.ca (only one @ sign) But brings back svanderwaal@l@rainbow.ca THE LAST LETTER OF MAIN EMAIL IS Going between two @ signs.
Another example is....
FirstName LastName CustomerNo OppNo note
KeithZwar$011730 163864 Equipment summary: Manufacturer: CATERPILLAR Product Family: TRACK EXCAVATORS Model: 330DL Serial number: MWP01014 Catalog Number: CU1553210 Price: $ 199,500 USD URL: http://dsf.catused.cat.com/dealer/equipment/view-equipment-detail.html.arg=equipmentPK%3DEq1.2664321W My Contact Information: Name: Stacy Huo Country: Jamaica Province: Not available City: Runaway Bay Email: housing_project@163.com Telephone: 1 876 3947753 Thank you,
WHICH should bring back email = housing_project@163.com (only one @sign) but it brings back housing_project@t@163.com Telephone:
January 4, 2013 at 3:15 pm
It has something to do with this part of the email
+
LEFT(TRAILING, CHARINDEX(' ', Trailing + ' ') - 1)
because if I take this out I get the beginning correctly for most of them.
January 4, 2013 at 3:26 pm
Got it it was the defining of trailing should be PosOfAt + 1
January 4, 2013 at 3:52 pm
Using my examples plus this one I have one more problem I get more information that is needed fort he emai sometimes. I get for example 2 I am getting the word Telephone: after it which it needs to stop as soon as it gets to a space. should be stopping after .com. I am having the similar problem with this one but at the beginning instead.
FirstNameLastNameCustomerNoOppNonoteLeadingemail
KeithZwar$004055125125 Please Let us know if you can supply a Cat 16H grader from 1996 up totally overhauled/rebuilt. Best Regards: Paul Crolla Santina Machinery Oslo- Norway Tel: +47-22.202068 Fax: +44-161.226.8525 santina@online.no Mario Crolla Santina Machinery Manchester- England Tel: +44-161.226.8525 Fax: +44-161.226.8525 m.crolla@sky.com Please Let us know if you can supply a Cat 16H grader from 1996 up totally overhauled/rebuilt. Best Regards: Paul Crolla Santina Machinery Oslo- Norway Tel: +47-22.202068 Fax: +44-161.226.8525 santina
email is coming out as '+44-161.226.8525 santina@online.no' but should be only santina@online.no
Help me please I know it is somewhere in the Cross Appply section but I don't know where.
January 4, 2013 at 6:21 pm
tbmabry (1/4/2013)
Using my examples plus this one I have one more problem I get more information that is needed fort he emai sometimes. I get for example 2 I am getting the word Telephone: after it which it needs to stop as soon as it gets to a space. should be stopping after .com. I am having the similar problem with this one but at the beginning instead.FirstNameLastNameCustomerNoOppNonoteLeadingemail
KeithZwar$004055125125 Please Let us know if you can supply a Cat 16H grader from 1996 up totally overhauled/rebuilt. Best Regards: Paul Crolla Santina Machinery Oslo- Norway Tel: +47-22.202068 Fax: +44-161.226.8525 santina@online.no Mario Crolla Santina Machinery Manchester- England Tel: +44-161.226.8525 Fax: +44-161.226.8525 m.crolla@sky.com Please Let us know if you can supply a Cat 16H grader from 1996 up totally overhauled/rebuilt. Best Regards: Paul Crolla Santina Machinery Oslo- Norway Tel: +47-22.202068 Fax: +44-161.226.8525 santina
email is coming out as '+44-161.226.8525 santina@online.no' but should be only santina@online.no
Help me please I know it is somewhere in the Cross Appply section but I don't know where.
I copied and pasted your note from above into my sample test harness as so:
;WITH Notes (Note) AS (
SELECT 'Please Let us know if you can supply a Cat 16H grader from 1996 up totally overhauled/rebuilt. Best Regards: Paul Crolla Santina Machinery Oslo- Norway Tel: +47-22.202068 Fax: +44-161.226.8525 santina@online.no Mario Crolla Santina Machinery Manchester- England Tel: +44-161.226.8525 Fax: +44-161.226.8525 m.crolla@sky.com Please Let us know if you can supply a Cat 16H grader from 1996 up totally overhauled/rebuilt. Best Regards: Paul Crolla Santina Machinery Oslo- Norway Tel: +47-22.202068 Fax: +44-161.226.8525'
--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)
WHERE Note LIKE '%[0-9A-Za-z.][@][0-9A-Za-z.]%'
The result I get is:
I really don't mean to sound condescending, but you are using copy/paste to include the CROSS APPLYs into your code right? I mean, the extra space in the PATINDEX pattern and the wrong formula for PosOfAt suggest that you are not.
The above is kind of an interesting case as it contains 2 emails. The CROSS APPLYs method shown above only returns 1 of them.
One other possibility is that the character that precedes the email only looks like a blank but is some special character.
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 6:27 pm
At the risk of further confusing this thing, I'm going to offer you another alternative.
See the 4th article in my signature string (Pattern Splitting) to find a FUNCTION called PatternSplitCM as used below.
;WITH Notes (Note) AS (
SELECT 'Please Let us know if you can supply a Cat 16H grader from 1996 up totally overhauled/rebuilt. Best Regards: Paul Crolla Santina Machinery Oslo- Norway Tel: +47-22.202068 Fax: +44-161.226.8525 santina@online.no Mario Crolla Santina Machinery Manchester- England Tel: +44-161.226.8525 Fax: +44-161.226.8525 m.crolla@sky.com Please Let us know if you can supply a Cat 16H grader from 1996 up totally overhauled/rebuilt. Best Regards: Paul Crolla Santina Machinery Oslo- Norway Tel: +47-22.202068 Fax: +44-161.226.8525'
--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 item
FROM Notes
CROSS APPLY PatternSplitCM(Note, '[0-9A-Z@.]')
WHERE Note LIKE '%[0-9A-Za-z.][@][0-9A-Za-z.]%' AND [Matched] = 1 AND CHARINDEX('@', Item) <> 0
The benefit of this approach is that it will retrieve both emails. I believe it will also work if the characters before and after the email are not spaces but anything excluded by the pattern.
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 7, 2013 at 4:58 pm
Thanks for you help. So would the function go on the top of the proc? not sure what you did here. but thanks.
January 7, 2013 at 5:20 pm
Here is the FUNCTION referenced in the article. Copy/paste this code into an SSMS window on your database and execute the script.
-- PatternSplitCM will split a string based on a pattern of the form
-- supported by LIKE and PATINDEX
--
-- Created by: Chris Morris 12-Oct-2012
CREATE FUNCTION PatternSplitCM
(
@List VARCHAR(8000) = NULL
,@Pattern VARCHAR(50)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH numbers AS (
SELECT TOP(ISNULL(DATALENGTH(@List), 0))
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
Item = SUBSTRING(@List,MIN(n),1+MAX(n)-MIN(n)),
[Matched]
FROM (
SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
FROM numbers
CROSS APPLY (
SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,1) LIKE @Pattern THEN 1 ELSE 0 END
) y
) d
GROUP BY [Matched], Grouper
Then copy/paste the code I gave you prior, execute that and examine the results. Should do exactly what you need.
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 - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply