473,396 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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 5138
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: SomeDude | last post by:
Lo group, I would like to know if it is possible to a (string) replace on existing records based on a given pattern. Let's say I have a table containing the following records (strings):...
3
by: Timmy | last post by:
I'm working on a simple click-through image gallery and I have images with captions in two arrays like this: var current_value="0"; var images = new Array ("photo01.jpg", "photo02.jpg",...
2
by: HB2 | last post by:
I use to be able to highlight several records in a table, copy them and paste new records. Now when I right click the records the copy and paste functions are disabled. How do I enable them? ...
5
by: JackRazz | last post by:
Anyone know the regular expression to match a blank line where the byte sequence is "0D 0A 0D 0A" ive tried "\r\n\r\n+", "^$+" "\n\r" with no success. Any Ideas? Thanks - JackRazz This is...
6
by: likong | last post by:
Hi, Any idea about how to write a regular expression that matches a substring xxx as long as the string does NOT contain substring yyy? Thanks. Kong
4
by: jjouett | last post by:
I have the following input XML: <?xml version="1.0"?> <ordersubmit xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"...
1
by: JJ | last post by:
Is it possible to count the number of columns that match certain conditions and return a single value? For example in the database we have a record where: Column 1 = Male Column 2 = A Column...
2
by: Rob | last post by:
I've just about beat my brains all the way out on this one. Is anyone up to the amazingly ridiculous challenge of figuring this one out? Here's the code, followed by my question: <script...
2
by: metaperl | last post by:
First a simple question: Is it allowed to provide a date range that has an illegal date. For instance SELECT * FROM tbl WHERE datex >= 2007-09-01 AND datex <= 2007-09-31 Now for my real...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.