UPS Tracking Numbers

[sparkbyte]

Does anyone know of a SP or a SQL query that can be used to parse UPS tracing Numbers?

UPS tracking Number format seems to be

Prefix Shipper Shipping Parsel
Act Number Method Number

1Z 1A1234 01 12345678

So a complete tracking number would be like
1Z1A12340112345678

Shipping method can be... (from what I know so far)

01 = Overnight
02 = 2nd Day
03 = Ground

So I need to something to create a temp table like

RecordNumber as Numeric(10)
TrackingNumber as char(18)
TrackingDate as DateTime
TrackingNumberPrefix as char(2)
TrackingNumberAct as char(6)
TrackingNumberShipping as Numeric(2)
TrackingNumberParsel as Numeric(8)

Then

Parse TackingNumber and update table.

So that I can do something like....

Select RecordNumber,
TrackingNumber,
TrackingDate
From @tblTemp
Where TrackingNumberPrefix = '1Z' AND TrackingNumberShipping = '01'




Thanks

John Fuhrman

 

[sparkbyte]

OK,

Here is where I am now.

Create the new table
Copy the needed data into the table.

Create Table tblTrackingParse
	( Tracking_ID int
	, NetworkLogonID varchar(50)
	, MachineName varchar(20)
	, BoxNumber varchar(45)
	, FileNumber varchar(25)
	, TrackingDate Datetime
	, TrackingNumberPrefix char(2)
	, TrackingNumberAct char(6) 
	, TrackingNumberShipping Numeric(2)
	, TrackingNumberParsel Numeric(8)
	)


Insert Into tblTrackingParse
	( Tracking_ID
	, NetworkLogonID
	, MachineName
	, BoxNumber
	, FileNumber
	, TrackingDate
	)
Select 
	  Tracking_ID
	, NetworkLogonID
	, MachineName
	, BoxNumber
	, FileNumber
	, TrackingDate
From dbo.TrackingTable

Now I need some help with parsing out the UPS tracking number.

THANKS!!!!!!

Thanks

John Fuhrman

 

[RiverGuy]

All you need is the SUBSTRING function. I used LEFT and RIGHT in this example as well.

DECLARE @S VARCHAR(100)
SELECT @S = '1Z1A12340112345678'

SELECT 
LEFT(@S, 2) AS Prefix,
SUBSTRING(@S, 3, 6) AS ShipperActNumber,
SUBSTRING(@S, 9, 2) AS ShippingMethod,
RIGHT(@S, 8) AS ParselNumber

 

[sparkbyte]

OK, Thanks!!

got it.

USE Mailroom

GO

Create Table tblTrackingParse
	( Tracking_ID int
	, NetworkLogonID varchar(50)
	, MachineName varchar(20)
	, BoxNumber varchar(45)
	, FileNumber varchar(25)
	, TrackingDate Datetime
	, TrackingNumberPrefix varchar(2)
	, TrackingNumberAct varchar(6) 
	, TrackingNumberShipping varchar(2)
	, TrackingNumberParsel varchar(8)
	)

GO

Insert Into tblTrackingParse
	( Tracking_ID
	, NetworkLogonID
	, MachineName
	, BoxNumber
	, FileNumber
	, TrackingDate
	)
Select 
	  Tracking_ID
	, NetworkLogonID
	, MachineName
	, BoxNumber
	, FileNumber
	, TrackingDate
From dbo.TrackingTable

GO

update dbo.tblTrackingParse
Set TrackingNumberPrefix = Upper(substring(BoxNumber,1,2))
	, TrackingNumberAct = Upper(substring(BoxNumber,3,6))
	, TrackingNumberShipping = Upper(substring(BoxNumber,9,2))
	, TrackingNumberParsel = Upper(substring(BoxNumber,11,8))
Where substring(BoxNumber,1,2) = '1Z'

Select 
	  BoxNumber
	, FileNumber
	, TrackingDate
	, TrackingNumberPrefix
	, TrackingNumberAct
	, TrackingNumberShipping
	, TrackingNumberParsel
From dbo.tblTrackingParse
where TrackingNumberPrefix <> ''
--	AND TrackingNumberShipping > '03'
	AND FileNumber <> '.box.end.'

Thanks

John Fuhrman