469,602 Members | 1,882 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

How to match records on several substring functions

Hello All,
I have a sql statement that I'm using to match records on. But i need
to use a substring to get the nuber that I need, and I have to use
several substrings to get the number since its embedded in other text.
Here's my sql:
use fto_ups
select distinct o.Shipper#, o.InvoiceDate, o.InvoiceNumber, o.ChgType,
o.Lading, o.BillTo, o.Name2, o.Address1, o.Address2, o.City, o.State,
o.Zip,
o.ShipFromName1, o.ShipFromeName2, o.ShipFromAddress1,
o.ShipFromAddess2, o.ShipFromCity, o.ShipFromState, o.ShipFromZip,
o.PickupDate,o.PickUpRecord, o.TrackingNumber,
o.BOL, o.Reference2, o.ServiceType, o.ServiceZip, o.Zone, o.Weight,
o.PublishedCharge, o.IncentiveCredit, o.InsuredCharge, o.BilledCharge,
o.DelAreaSurcharge,
m.FRRDSSTrackingNbr,m.FRRDSSShipmentNbr, m.FRRDSSDivision,
m.FRRDSSDeptNbr, m.FRRDSSVendorNbr, m.FRRDSSChargeBackNbr,
m.FRRDSSShipmentCreateDate,m.[UPS Units],m.[UPS % Units to Total]
into dbo.tempOutBoundTable
from upsOutbound o left join msoutboundhistory m on right(o.bol,7) =
m.FRRDSSShipmentNbr and o.bol = m.FRRDSSShipmentNbr
********
How do I select the records using substring(o.bol,4,7) to add to the
above query? How do I inner join the records based on adding the
substring extracted o.bol?

Dec 13 '06 #1
3 4977
On 13 Dec 2006 09:47:01 -0800, "Rnt6872" <r_******@msn.comwrote:
>How do I select the records using substring(o.bol,4,7) to add to the
above query? How do I inner join the records based on adding the
substring extracted o.bol?
I don't know just what you are asking. The existing query already
uses a substring - well, RIGHT() in this case, but the same idea - to
perform the LEFT OUTER JOIN.

FROM upsOutbound o
LEFT OUTER
JOIN msoutboundhistory m
ON right(o.bol,7) = m.FRRDSSShipmentNbr
AND o.bol = m.FRRDSSShipmentNbr

If you have another test for substring(o.bol,4,7) you should be able
to use the same approach but with substring() instead of right().

Roy Harvey
Beacon Falls, CT
Dec 13 '06 #2
Thank you. I'll try that.
Roy Harvey wrote:
On 13 Dec 2006 09:47:01 -0800, "Rnt6872" <r_******@msn.comwrote:
How do I select the records using substring(o.bol,4,7) to add to the
above query? How do I inner join the records based on adding the
substring extracted o.bol?

I don't know just what you are asking. The existing query already
uses a substring - well, RIGHT() in this case, but the same idea - to
perform the LEFT OUTER JOIN.

FROM upsOutbound o
LEFT OUTER
JOIN msoutboundhistory m
ON right(o.bol,7) = m.FRRDSSShipmentNbr
AND o.bol = m.FRRDSSShipmentNbr

If you have another test for substring(o.bol,4,7) you should be able
to use the same approach but with substring() instead of right().

Roy Harvey
Beacon Falls, CT
Dec 13 '06 #3
... if I may add:
Try avoid using joins on substrings. This can harm your performance in
the future, while the server can no operate with indexes. That means,
even you will have an index on that filed, it will be not used, afaik.

Best regards

Matik
Rnt6872 napisal(a):
Thank you. I'll try that.
Dec 14 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Timmy | last post: by
2 posts views Thread by HB2 | last post: by
5 posts views Thread by JackRazz | last post: by
2 posts views Thread by Rob | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.