Use Outer Apply to parse a delimited file

Last weekend I tried ice fishing for the first time this winter. The day was great, sunny and temps in the high 30s, but the fish weren’t biting. Not only did we not see a fish, but we didn’t see anyone else on the lake catch anything. Back in the office this week I talked to a few other men who had gone out to different lakes and everyone had the same results. It ended up just being a bad weekend to fish. I also ran into an issue where some ice that I stepped on sank perceptably under my feet. The ice we were fishing was 10 – 12 inches thick, but it still wasn’t great ice. Down in Lake Geneva this weekend, over 50 cars sank through the ice into the lake this weekend as well. Remember to always be careful when ice fishing, even if it appears safe.
I ran into an interesting problem this week where I had to parse a semi-colon delimited file into different data fields. The interesting part was if there were no values for a field, it was excluded from the results. This resulted in a different number of total columns for each row. My first step was to import that data into a table. Here’s what it looked like after I loaded it:

2016-02-09 06:35:43,923 [WT-2 ] TRACE AdiServer.Services.RateNShip - [HandleSingleService] before calling Ship
2016-02-09 06:35:43,923 [WT-2 ] TRACE Fsms.Interface.FsmsServer - [Ship] Shipping the shipment
2016-02-09 06:35:43,923 [WT-2 ] TRACE Fsms.Interface.OpenShipHelper - [ProcessOpenShipShipment] FSMS Request: 0,"20"4,""5,"182 S. County Fake 4 E"7,"Avone"8,"AK"9,"46133"23,"1"32,"Tammy Fake"50,"US"75,"LBS"79,"Return Package"117,"US"183,"317-555-5555"498,"54224591"541,"YNNNNasdfNNNN"542,"002693156062"1273,"1"16570,"10"22510,"2680079"24175,"7"30557,"26805079"995,""
2016-02-09 06:35:43,922 [WT-2 ] DEBUG AdiServer.Service.MW.WorkerThread - param: 0001CONSIGNEE_CONTACT=Tammy Fake;0001CONSIGNEE_ADDRESS1=182 S. County Fake 4 E;0001CONSIGNEE_CITY=Avone;0001CONSIGNEE_STATE=AK;0001CONSIGNEE_POSTALCODE=46133;0001CONSIGNEE_PHONE=317-555-5555;0001CONSIGNEE_EMAIL=fake.fake@phonxak.com;0002COUNTRY=United States;0006SERVICEPLAINTEXT=FedEx;0101MANIFEST=HOLD;0000RMA_NUMBER=2365806079;0000ReturnDeliveryMethod=Email;0000ComplimentaryReturn=false;0000NatureOfReturn=Refund;0000FSMS_GENERATE_PDF_LABEL=True;0000FSMS_PDF_DESTINATION=C:\ADI\ADILabels;0000FSMS_PDF_SIZE=PDF;1003TERMS=Prepaid;1005SHIPPER=Return;1000SHIPPER_REFERENCE=2680045679;1000SHIPDATE=2/8/2012;1000WEIGHT=0.1;1000PACKAGING=CUSTOM;1000DESCRIPTION=Return Package;1000RETURN_DELIVERY=TRUE;
2016-02-09 06:35:43,924 [WT-2 ] TRACE Fsms.Interface.OpenShipHelper - [ProcessOpenShipShipment] actual PR index: 0
2016-02-09 06:35:43,924 [WT-2 ] TRACE Fsms.Interface.Communications.FsmsSocketClient - [SendFsmsString] request: 0,"20"4,""5,"182 S. County Fake 900 E"7,"Avone"8,"AK"9,"46145623"23,"1"32,"Tammy Fake"50,"US"75,"LBS"79,"Return Package"117,"US"183,"317-504459-19786795"498,"5426291"541,"YNNNNasdfNNNN"542,"00269376062"1273,"1"164570,"10"222410,"2680456079"2417,"7"305647,"2680456079"99,""

All the records I’m interested in follow the format of the record above that is bolded and italicized above.  To parse the table, I need to divide it into three sections – the first will be the log date and time, the second will be some filler information that I don’t need, and the third will be the section that I want to delimit.

SELECT  LEFT(AllFields, 19) AS LogDateTime ,
        SUBSTRING(AllFields, 20, 18) AS Filler1 ,
        SUBSTRING(AllFields, 39, 7961) AS Remainder
FROM    dbo.ADI_Log_Staging;

I’m going to put those three fields into a common table expression, and then use the third field to parse.

WITH    cte
           AS ( SELECT   LEFT(AllFields, 19) AS LogDateTime ,
                       SUBSTRING(AllFields, 20, 18) AS Filler1 ,
                       SUBSTRING(AllFields, 39, 7961) AS Remainder
               FROM     dbo.ADSI_Log_Staging
               )

I needed to only get the specific records I was looking for related to returns shipping, so I added the WHERE clause to look in the Remainder cte field for a specific pattern. I also wanted to get the LogDateTime, and I knew the first field would always be the contact info. My query had now evolved to:

WITH    cte
           AS ( SELECT   LEFT(AllFields, 19) AS LogDateTime ,
                       SUBSTRING(AllFields, 20, 18) AS Filler1 ,
                       SUBSTRING(AllFields, 39, 7961) AS Remainder
               FROM     dbo.ADSI_Log_Staging
               )
   SELECT  SUBSTRING(cte.Remainder,44,CHARINDEX(';',cte.Remainder,44)-44) AS Contact,
           cte.LogDateTime
   FROM    cte
   WHERE   LEFT(cte.Remainder, 66) = 'AdsiServer.Service.MW.WorkerThread - param: 0001CONSIGNEE_CONTACT='

This got me all the things listed above, as seen here:

20160215 Outer Apply first query results

Next I needed to get all the additional data.  Sometimes the second delimited field will hold an address, but sometimes it won’t.  I’m going to parse the results into generic fields first, then test each field to see which piece of information it contains.  Here is some extra logic added to the query:

WITH    cte
           AS ( SELECT   LEFT(AllFields, 19) AS LogDateTime ,
                       SUBSTRING(AllFields, 20, 18) AS Filler1 ,
                       SUBSTRING(AllFields, 39, 7961) AS Remainder
               FROM     dbo.ADSI_Log_Staging
               )
   SELECT  SUBSTRING(cte.Remainder,44,CHARINDEX(';',cte.Remainder,44)-44) AS Contact,
           SUBSTRING(cte.Remainder,P1.POS,P2.POS - P1.POS) AS Field2,
           SUBSTRING(cte.Remainder,P2.POS,P3.POS - P2.POS) AS Field3,
           SUBSTRING(cte.Remainder,P3.POS,P4.POS - P3.POS) AS Field4,
           SUBSTRING(cte.Remainder,P4.POS,P5.POS - P4.POS) AS Field5,
           SUBSTRING(cte.Remainder,P5.POS,P6.POS - P5.POS) AS Field6,
           cte.LogDateTime
   FROM    cte
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder)) POS) AS P1
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P1.POS+1)) POS) AS P2 
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P2.POS+1)) POS) AS P3
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P3.POS+1)) POS) AS P4
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P4.POS+1)) POS) AS P5
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P5.POS+1)) POS) AS P6
   WHERE   LEFT(cte.Remainder, 66) = 'AdsiServer.Service.MW.WorkerThread - param: 0001CONSIGNEE_CONTACT='

Now I’ll explain what’s happening here. I’m generating the location of each semicolon using the CROSS APPLY joins. Each one gets the position of the previous semicolumn, then uses CHARINDEX to start searching for the next one, beginning with the first character after the current one. So P1 is giving me the position of the first semi-colon in the string, P2 has the second, P3 has the third, and so on. To display the fields, I’m using the SUBSTRING function, starting at the position of the current semicolon and going to the position of the next semicolon. Here’s a look at the new results:

20160215 Outer Apply second query results.PNG
To check which column each field should go into, I used a set of extensive CASE statements:

SELECT RIGHT(chopped.Contact,CHARINDEX('=',REVERSE(chopped.Contact))-1) AS Contact,
   CASE WHEN LEFT(chopped.Field2,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field2,CHARINDEX('=',REVERSE(chopped.Field2))-1)
   WHEN LEFT(chopped.Field3,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field3,CHARINDEX('=',REVERSE(chopped.Field3))-1)
   WHEN LEFT(chopped.Field4,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1) END AS Address1,
   CASE WHEN LEFT(chopped.Field2,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field2,CHARINDEX('=',REVERSE(chopped.Field2))-1)
   WHEN LEFT(chopped.Field3,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field3,CHARINDEX('=',REVERSE(chopped.Field3))-1)
   WHEN LEFT(chopped.Field4,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1) END AS Address2,
   CASE WHEN LEFT(chopped.Field2,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field2,CHARINDEX('=',REVERSE(chopped.Field2))-1)
   WHEN LEFT(chopped.Field3,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field3,CHARINDEX('=',REVERSE(chopped.Field3))-1)
   WHEN LEFT(chopped.Field4,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1) END AS City,
With all this logic in mind, I set up my final query to insert from the staging table into the permanent table.  I did run into an interesting issue when I first tried to insert everything with a single statement with two common table expressions:
20160215 Error

I ended up having to insert my initial results into a temp table, then inserting from the temp table into the permanent table from there. Here is the final code:

DECLARE @CaptureDate DATETIME
   SELECT @CaptureDate = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0);

   WITH    cte
             AS ( SELECT   LEFT(AllFields, 19) AS LogDateTime ,
                           SUBSTRING(AllFields, 20, 18) AS Filler1 ,
                           SUBSTRING(AllFields, 39, 7961) AS Remainder
                  FROM     dbo.ADSI_Log_Staging
                )
   SELECT  SUBSTRING(cte.Remainder,44,CHARINDEX(';',cte.Remainder,44)-44) AS Contact,
           SUBSTRING(cte.Remainder,P1.POS,P2.POS - P1.POS) AS Field2,
           SUBSTRING(cte.Remainder,P2.POS,P3.POS - P2.POS) AS Field3,
           SUBSTRING(cte.Remainder,P3.POS,P4.POS - P3.POS) AS Field4,
           SUBSTRING(cte.Remainder,P4.POS,P5.POS - P4.POS) AS Field5,
           SUBSTRING(cte.Remainder,P5.POS,P6.POS - P5.POS) AS Field6,
           SUBSTRING(cte.Remainder,P6.POS,P7.POS - P6.POS) AS Field7,
           SUBSTRING(cte.Remainder,P7.POS,P8.POS - p7.POS) AS Field8,
           SUBSTRING(cte.Remainder,P8.POS,P9.POS - P8.POS) AS Field9,
           SUBSTRING(cte.Remainder,P9.POS,P10.POS - P9.POS) AS Field10,
           SUBSTRING(cte.Remainder,P10.POS,P11.POS - P10.POS) AS Field11,
           SUBSTRING(cte.Remainder,P11.POS,P12.POS - P11.POS) AS Field12,
           SUBSTRING(cte.Remainder,P12.POS,P13.POS - P12.POS) AS Field13,
           SUBSTRING(cte.Remainder,P13.POS,P14.POS - P13.POS) AS Field14,
           SUBSTRING(cte.Remainder,P14.POS,CASE WHEN P15.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P15.POS END - P14.POS) AS Field15,
           SUBSTRING(cte.Remainder,P15.POS,CASE WHEN P16.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P16.POS END - P15.POS) AS Field16,
           SUBSTRING(cte.Remainder,P16.POS,CASE WHEN P17.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P17.POS END - P16.POS) AS Field17,
           SUBSTRING(cte.Remainder,P17.POS,CASE WHEN P18.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P18.POS END - P17.POS) AS Field18,
           SUBSTRING(cte.Remainder,P18.POS,CASE WHEN P19.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P19.POS END - P18.POS) AS Field19,
           SUBSTRING(cte.Remainder,P19.POS,CASE WHEN P20.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P20.POS END - P19.POS) AS Field20,
           SUBSTRING(cte.Remainder,P20.POS,CASE WHEN P21.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P21.POS END - P20.POS) AS Field21,
           SUBSTRING(cte.Remainder,P21.POS,CASE WHEN P22.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P22.POS END - P21.POS) AS Field22,
           SUBSTRING(cte.Remainder,P22.POS,CASE WHEN P23.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P23.POS END - P22.POS) AS Field23,
           SUBSTRING(cte.Remainder,P23.POS,CASE WHEN P24.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P24.POS END - P23.POS) AS Field24,
           SUBSTRING(cte.Remainder,P24.POS,CASE WHEN P25.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P25.POS END - P24.POS) AS Field25,
           SUBSTRING(cte.Remainder,P25.POS,CASE WHEN P26.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P26.POS END - P25.POS) AS Field26,
           SUBSTRING(cte.Remainder,P26.POS,CASE WHEN P27.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P27.POS END - P26.POS) AS Field27,
           SUBSTRING(cte.Remainder,P27.POS,CASE WHEN P28.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P28.POS END - P27.POS) AS Field28,
           SUBSTRING(cte.Remainder,P28.POS,CASE WHEN P29.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P29.POS END - P28.POS) AS Field29,
           SUBSTRING(cte.Remainder,P29.POS,CASE WHEN P30.POS = 0 THEN LEN(cte.Remainder)+1 ELSE P30.POS END - P29.POS) AS Field30,
           cte.LogDateTime
   INTO #temp
   FROM    cte
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder)) POS) AS P1
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P1.POS+1)) POS) AS P2 
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P2.POS+1)) POS) AS P3
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P3.POS+1)) POS) AS P4
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P4.POS+1)) POS) AS P5
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P5.POS+1)) POS) AS P6
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P6.POS+1)) POS) AS P7
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P7.POS+1)) POS) AS P8
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P8.POS+1)) POS) AS P9
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P9.POS+1)) POS) AS P10
   CROSSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P10.POS+1)) POS) AS P11
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P11.POS+1)) POS) AS P12
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P12.POS+1)) POS) AS P13
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P13.POS+1)) POS) AS P14
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P14.POS+1)) POS) AS P15
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P15.POS+1)) POS) AS P16
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P16.POS+1)) POS) AS P17
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P17.POS+1)) POS) AS P18
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P18.POS+1)) POS) AS P19
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P19.POS+1)) POS) AS P20
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P20.POS+1)) POS) AS P21
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P21.POS+1)) POS) AS P22
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P22.POS+1)) POS) AS P23
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P23.POS+1)) POS) AS P24
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P24.POS+1)) POS) AS P25
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P25.POS+1)) POS) AS P26
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P26.POS+1)) POS) AS P27
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P27.POS+1)) POS) AS P28
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P28.POS+1)) POS) AS P29
   CROSS APPLY (SELECT (CHARINDEX(';',cte.Remainder,P29.POS+1)) POS) AS P30
   WHERE   LEFT(cte.Remainder, 66) = 'AdsiServer.Service.MW.WorkerThread - param: 0001CONSIGNEE_CONTACT='INSERT INTO dbo.ADSILogData
           ( NAME ,
             Address1 ,
             Address2 ,
             City ,
             STATE ,
             ZIP ,
             Phone ,
             Email ,
             Country ,
             ServicePlainTaxt ,
             Manifest ,
             RMA_Number ,
             ReturnDeliveryMethod ,
             ComplimentaryReturn ,
             NatureOfReturn ,
             FMS_GENERATE_PDF_LABEL ,
             FMS_PDF_Destination ,
             FMS_PDF_Size ,
             Terms ,
             Shipper ,
             Shipper_Reference ,
             ShipDate ,
             Weight ,
             Packaging ,
             DESCRIPTION ,
             Return_Delivery ,
             LogDateTime,
             LoadDate
           )
   SELECT RIGHT(chopped.Contact,CHARINDEX('=',REVERSE(chopped.Contact))-1) AS Contact,
   CASE WHEN LEFT(chopped.Field2,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field2,CHARINDEX('=',REVERSE(chopped.Field2))-1)
   WHEN LEFT(chopped.Field3,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field3,CHARINDEX('=',REVERSE(chopped.Field3))-1)
   WHEN LEFT(chopped.Field4,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,24) = ';0001CONSIGNEE_ADDRESS1=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1) END AS Address1,
   CASE WHEN LEFT(chopped.Field2,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field2,CHARINDEX('=',REVERSE(chopped.Field2))-1)
   WHEN LEFT(chopped.Field3,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field3,CHARINDEX('=',REVERSE(chopped.Field3))-1)
   WHEN LEFT(chopped.Field4,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,24) = ';0001CONSIGNEE_ADDRESS2=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1) END AS Address2,
   CASE WHEN LEFT(chopped.Field2,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field2,CHARINDEX('=',REVERSE(chopped.Field2))-1)
   WHEN LEFT(chopped.Field3,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field3,CHARINDEX('=',REVERSE(chopped.Field3))-1)
   WHEN LEFT(chopped.Field4,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,20) = ';0001CONSIGNEE_CITY=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1) END AS City,
   CASE WHEN LEFT(chopped.Field2,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field2,CHARINDEX('=',REVERSE(chopped.Field2))-1)
   WHEN LEFT(chopped.Field3,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field3,CHARINDEX('=',REVERSE(chopped.Field3))-1)
   WHEN LEFT(chopped.Field4,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1))
   WHEN LEFT(chopped.Field9,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field9,CHARINDEX('=',REVERSE(chopped.Field9))-1)
   WHEN LEFT(chopped.Field10,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field10,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,21) = ';0001CONSIGNEE_STATE=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1) END AS State,
   CASE WHEN LEFT(chopped.Field2,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field2,CHARINDEX('=',REVERSE(chopped.Field2))-1)
   WHEN LEFT(chopped.Field3,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field3,CHARINDEX('=',REVERSE(chopped.Field3))-1)
   WHEN LEFT(chopped.Field4,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1)
   WHEN LEFT(chopped.Field9,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field9,CHARINDEX('=',REVERSE(chopped.Field9))-1)
   WHEN LEFT(chopped.Field10,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field10,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,26) = ';0001CONSIGNEE_POSTALCODE=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1) END AS ZIP,
   CASE WHEN LEFT(chopped.Field3,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field3,CHARINDEX('=',REVERSE(chopped.Field3))-1)
   WHEN LEFT(chopped.Field4,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1)
   WHEN LEFT(chopped.Field9,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field9,CHARINDEX('=',REVERSE(chopped.Field9))-1)
   WHEN LEFT(chopped.Field10,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field10,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1)
   WHEN LEFT(chopped.Field12,21) = ';0001CONSIGNEE_PHONE=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1) END AS PHONE,
   CASE WHEN LEFT(chopped.Field4,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field4,CHARINDEX('=',REVERSE(chopped.Field4))-1)
   WHEN LEFT(chopped.Field5,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1)
   WHEN LEFT(chopped.Field9,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field9,CHARINDEX('=',REVERSE(chopped.Field9))-1)
   WHEN LEFT(chopped.Field10,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field10,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1)
   WHEN LEFT(chopped.Field12,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1)
   WHEN LEFT(chopped.Field13,21) = ';0001CONSIGNEE_EMAIL=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1) END AS Email,
   CASE WHEN LEFT(chopped.Field5,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field5,CHARINDEX('=',REVERSE(chopped.Field5))-1)
   WHEN LEFT(chopped.Field6,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1)
   WHEN LEFT(chopped.Field9,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field9,CHARINDEX('=',REVERSE(chopped.Field9))-1)
   WHEN LEFT(chopped.Field10,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field10,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1)
   WHEN LEFT(chopped.Field12,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1)
   WHEN LEFT(chopped.Field13,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1)
   WHEN LEFT(chopped.Field14,13) = ';0002COUNTRY=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1) END AS Country,
CASE WHEN LEFT(chopped.Field6,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field6,CHARINDEX('=',REVERSE(chopped.Field6))-1)
   WHEN LEFT(chopped.Field7,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1)
   WHEN LEFT(chopped.Field9,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field9,CHARINDEX('=',REVERSE(chopped.Field9))-1)
   WHEN LEFT(chopped.Field10,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field10,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1)
   WHEN LEFT(chopped.Field12,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1)
   WHEN LEFT(chopped.Field13,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1)
   WHEN LEFT(chopped.Field14,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1)
   WHEN LEFT(chopped.Field15,22) = ';0006SERVICEPLAINTEXT=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1) END AS ServicePlainText,
   CASE WHEN LEFT(chopped.Field7,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field7,CHARINDEX('=',REVERSE(chopped.Field7))-1)
   WHEN LEFT(chopped.Field8,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1)
   WHEN LEFT(chopped.Field9,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field9,CHARINDEX('=',REVERSE(chopped.Field9))-1)
   WHEN LEFT(chopped.Field10,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field10,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1)
   WHEN LEFT(chopped.Field12,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1)
   WHEN LEFT(chopped.Field13,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1)
   WHEN LEFT(chopped.Field14,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1)
   WHEN LEFT(chopped.Field15,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1)
   WHEN LEFT(chopped.Field16,14) = ';0101MANIFEST=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1) END AS Manifest,
   CASE WHEN LEFT(chopped.Field8,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field8,CHARINDEX('=',REVERSE(chopped.Field8))-1)
   WHEN LEFT(chopped.Field9,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field9,CHARINDEX('=',REVERSE(chopped.Field9))-1)
   WHEN LEFT(chopped.Field10,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field10,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1)
   WHEN LEFT(chopped.Field12,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1)
   WHEN LEFT(chopped.Field13,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1)
   WHEN LEFT(chopped.Field14,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1)
   WHEN LEFT(chopped.Field15,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1)
   WHEN LEFT(chopped.Field16,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1)
   WHEN LEFT(chopped.Field17,16) = ';0000RMA_NUMBER=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1) END AS RMA_number,
   CASE WHEN LEFT(chopped.Field9,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field9,CHARINDEX('=',REVERSE(chopped.Field9))-1)
   WHEN LEFT(chopped.Field10,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field110,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1)
   WHEN LEFT(chopped.Field12,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1)
   WHEN LEFT(chopped.Field13,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1)
   WHEN LEFT(chopped.Field14,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1)
   WHEN LEFT(chopped.Field15,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1)
   WHEN LEFT(chopped.Field16,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1)
   WHEN LEFT(chopped.Field17,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1)
   WHEN LEFT(chopped.Field18,26) = ';0000ReturnDeliveryMethod=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1) END AS ReturnDeliveryMethod,
   CASE WHEN LEFT(chopped.Field10,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field10,CHARINDEX('=',REVERSE(chopped.Field10))-1)
   WHEN LEFT(chopped.Field11,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1)
   WHEN LEFT(chopped.Field12,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1)
   WHEN LEFT(chopped.Field13,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1)
   WHEN LEFT(chopped.Field14,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1)
   WHEN LEFT(chopped.Field15,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1)
   WHEN LEFT(chopped.Field16,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1)
   WHEN LEFT(chopped.Field17,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1)
   WHEN LEFT(chopped.Field18,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1)
   WHEN LEFT(chopped.Field19,25) = ';0000ComplimentaryReturn=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1) END AS ComplimentaryReturn,
   CASE WHEN LEFT(chopped.Field11,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field11,CHARINDEX('=',REVERSE(chopped.Field11))-1)
   WHEN LEFT(chopped.Field12,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1)
   WHEN LEFT(chopped.Field13,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1)
   WHEN LEFT(chopped.Field14,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1)
   WHEN LEFT(chopped.Field15,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1)
   WHEN LEFT(chopped.Field16,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1)
   WHEN LEFT(chopped.Field17,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1)
   WHEN LEFT(chopped.Field18,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1)
   WHEN LEFT(chopped.Field19,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1)
   WHEN LEFT(chopped.Field20,20) = ';0000NatureOfReturn=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1) END AS NatureofReturn,
CASE WHEN LEFT(chopped.Field12,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field12,CHARINDEX('=',REVERSE(chopped.Field12))-1)
   WHEN LEFT(chopped.Field13,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1)
   WHEN LEFT(chopped.Field14,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1)
   WHEN LEFT(chopped.Field15,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1)
   WHEN LEFT(chopped.Field16,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1)
   WHEN LEFT(chopped.Field17,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1)
   WHEN LEFT(chopped.Field18,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1)
   WHEN LEFT(chopped.Field19,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1)
   WHEN LEFT(chopped.Field20,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1)
   WHEN LEFT(chopped.Field21,29) = ';0000FSMS_GENERATE_PDF_LABEL=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(chopped.Field21))-1) END AS FSMS_Generate_PDF_Label,
   CASE WHEN LEFT(chopped.Field13,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field13,CHARINDEX('=',REVERSE(chopped.Field13))-1)
   WHEN LEFT(chopped.Field14,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1)
   WHEN LEFT(chopped.Field15,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1)
   WHEN LEFT(chopped.Field16,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1)
   WHEN LEFT(chopped.Field17,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1)
   WHEN LEFT(chopped.Field18,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1)
   WHEN LEFT(chopped.Field19,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1)
   WHEN LEFT(chopped.Field20,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1)
   WHEN LEFT(chopped.Field21,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(chopped.Field21))-1)
   WHEN LEFT(chopped.Field22,26) = ';0000FSMS_PDF_DESTINATION=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1) END AS FSMS_PDF_Destination,
   CASE WHEN LEFT(chopped.Field14,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field14,CHARINDEX('=',REVERSE(chopped.Field14))-1)
   WHEN LEFT(chopped.Field15,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1)
   WHEN LEFT(chopped.Field16,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1)
   WHEN LEFT(chopped.Field17,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1)
   WHEN LEFT(chopped.Field18,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1)
   WHEN LEFT(chopped.Field19,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1)
   WHEN LEFT(chopped.Field20,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1)
   WHEN LEFT(chopped.Field21,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(chopped.Field21))-1)
   WHEN LEFT(chopped.Field22,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1)
   WHEN LEFT(chopped.Field23,19) = ';0000FSMS_PDF_SIZE=' THEN RIGHT(chopped.Field23,CHARINDEX('=',REVERRSE(chopped.Field23))-1) END AS FSMS_PDF_SIZE,
   CASE WHEN LEFT(chopped.Field15,11) = ';1003TERMS=' THEN RIGHT(chopped.Field15,CHARINDEX('=',REVERSE(chopped.Field15))-1)
   WHEN LEFT(chopped.Field16,11) = ';1003TERMS=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1)
   WHEN LEFT(chopped.Field17,11) = ';1003TERMS=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1)
   WHEN LEFT(chopped.Field18,11) = ';1003TERMS=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1)
   WHEN LEFT(chopped.Field19,11) = ';1003TERMS=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1)
   WHEN LEFT(chopped.Field20,11) = ';1003TERMS=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1)
   WHEN LEFT(chopped.Field21,11) = ';1003TERMS=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(chopped.Field21))-1)
   WHEN LEFT(chopped.Field22,11) = ';1003TERMS=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1)
   WHEN LEFT(chopped.Field23,11) = ';1003TERMS=' THEN RIGHT(chopped.Field23,CHARINDEX('=',REVERSE(chopped.Field23))-1)
   WHEN LEFT(chopped.Field24,11) = ';1003TERMS=' THEN RIGHT(chopped.Field24,CHARINDEX('=',REVERSE(chopped.Field24))-1) END AS Terms,
   CASE WHEN LEFT(chopped.Field16,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field16,CHARINDEX('=',REVERSE(chopped.Field16))-1)
   WHEN LEFT(chopped.Field17,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1)
   WHEN LEFT(chopped.Field18,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1)
   WHEN LEFT(chopped.Field19,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1)
   WHEN LEFT(chopped.Field20,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1)
   WHEN LEFT(chopped.Field21,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(chopped.Field21))-1)
   WHEN LEFT(chopped.Field22,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1)
   WHEN LEFT(chopped.Field23,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field23,CHARINDEX('=',REVERSE(chopped.Field23))-1)
   WHEN LEFT(chopped.Field24,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field24,CHARINDEX('=',REVERSE(chopped.Field24))-1)
   WHEN LEFT(chopped.Field25,13) = ';1005SHIPPER=' THEN RIGHT(chopped.Field25,CHARINDEX('=',REVERSE(chopped.Field25))-1) END AS Shipper,CASE WHEN LEFT(chopped.Field17,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field17,CHARINDEX('=',REVERSE(chopped.Field17))-1)
   WHEN LEFT(chopped.Field18,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1)
   WHEN LEFT(chopped.Field19,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1)
   WHEN LEFT(chopped.Field20,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1)
   WHEN LEFT(chopped.Field21,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(chopped.Field21))-1)
   WHEN LEFT(chopped.Field22,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1)
   WHEN LEFT(chopped.Field23,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field23,CHARINDEX('=',REVERSE(chopped.Field23))-1)
   WHEN LEFT(chopped.Field24,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field24,CHARINDEX('=',REVERSE(chopped.Field24))-1)
   WHEN LEFT(chopped.Field25,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field25,CHARINDEX('=',REVERSE(chopped.Field25))-1)
   WHEN LEFT(chopped.Field26,23) = ';1000SHIPPER_REFERENCE=' THEN RIGHT(chopped.Field26,CHARINDEX('=',REVERSE(chopped.Field26))-1) END AS ReferenceNum,
   CASE WHEN LEFT(chopped.Field18,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field18,CHARINDEX('=',REVERSE(chopped.Field18))-1)
   WHEN LEFT(chopped.Field19,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1)
   WHEN LEFT(chopped.Field20,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1)
   WHEN LEFT(chopped.Field21,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(chopped.Field21))-1)
   WHEN LEFT(chopped.Field22,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1)
   WHEN LEFT(chopped.Field23,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field23,CHARINDEX('=',REVERSE(chopped.Field23))-1)
   WHEN LEFT(chopped.Field24,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field24,CHARINDEX('=',REVERSE(chopped.Field24))-1)
   WHEN LEFT(chopped.Field25,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field25,CHARINDEX('=',REVERSE(chopped.Field25))-1)
   WHEN LEFT(chopped.Field26,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field26,CHARINDEX('=',REVERSE(chopped.Field26))-1)
   WHEN LEFT(chopped.Field27,14) = ';1000SHIPDATE=' THEN RIGHT(chopped.Field27,CHARINDEX('=',REVERSE(chopped.Field27))-1) END AS Shipdate,
   CASE WHEN LEFT(chopped.Field19,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field19,CHARINDEX('=',REVERSE(chopped.Field19))-1)
   WHEN LEFT(chopped.Field20,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1)
   WHEN LEFT(chopped.Field21,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(chopped.Field21))-1)
   WHEN LEFT(chopped.Field22,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1)
   WHEN LEFT(chopped.Field23,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field23,CHARINDEX('=',REVERSE(chopped.Field23))-1)
   WHEN LEFT(chopped.Field24,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field24,CHARINDEX('=',REVERSE(chopped.Field24))-1)
   WHEN LEFT(chopped.Field25,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field25,CHARINDEX('=',REVERSE(chopped.Field25))-1)
   WHEN LEFT(chopped.Field26,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field26,CHARINDEX('=',REVERSE(chopped.Field26))-1)
   WHEN LEFT(chopped.Field27,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field27,CHARINDEX('=',REVERSE(chopped.Field27))-1)
   WHEN LEFT(chopped.Field28,12) = ';1000WEIGHT=' THEN RIGHT(chopped.Field28,CHARINDEX('=',REVERSE(chopped.Field28))-1) END AS Weight,
   CASE WHEN LEFT(chopped.Field20,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field20,CHARINDEX('=',REVERSE(chopped.Field20))-1)
   WHEN LEFT(chopped.Field21,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(choppeed.Field21))-1)
   WHEN LEFT(chopped.Field22,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1)
   WHEN LEFT(chopped.Field23,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field23,CHARINDEX('=',REVERSE(chopped.Field23))-1)
   WHEN LEFT(chopped.Field24,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field24,CHARINDEX('=',REVERSE(chopped.Field24))-1)
   WHEN LEFT(chopped.Field25,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field25,CHARINDEX('=',REVERSE(chopped.Field25))-1)
   WHEN LEFT(chopped.Field26,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field26,CHARINDEX('=',REVERSE(chopped.Field26))-1)
   WHEN LEFT(chopped.Field27,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field27,CHARINDEX('=',REVERSE(chopped.Field27))-1)
   WHEN LEFT(chopped.Field28,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field28,CHARINDEX('=',REVERSE(chopped.Field28))-1)
   WHEN LEFT(chopped.Field29,15) = ';1000PACKAGING=' THEN RIGHT(chopped.Field29,CHARINDEX('=',REVERSE(chopped.Field29))-1) END AS Packaging,
   CASE WHEN LEFT(chopped.Field21,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field21,CHARINDEX('=',REVERSE(chopped.Field21))-1)
   WHEN LEFT(chopped.Field22,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1)
   WHEN LEFT(chopped.Field23,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field23,CHARINDEX('=',REVERSE(chopped.Field23))-1)
   WHEN LEFT(chopped.Field24,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field24,CHARINDEX('=',REVERSE(chopped.Field24))-1)
   WHEN LEFT(chopped.Field25,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field25,CHARINDEX('=',REVERSE(chopped.Field25))-1)
   WHEN LEFT(chopped.Field26,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field26,CHARINDEX('=',REVERSE(chopped.Field26))-1)
   WHEN LEFT(chopped.Field27,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field27,CHARINDEX('=',REVERSE(chopped.Field27))-1)
   WHEN LEFT(chopped.Field28,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field28,CHARINDEX('=',REVERSE(chopped.Field28))-1)
   WHEN LEFT(chopped.Field29,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field29,CHARINDEX('=',REVERSE(chopped.Field29))-1)
   WHEN LEFT(chopped.Field30,17) = ';1000DESCRIPTION=' THEN RIGHT(chopped.Field30,CHARINDEX('=',REVERSE(chopped.Field30))-1) END AS Description,
   CASE WHEN LEFT(chopped.Field22,21) = ';1000RETURN_DELIVERY=' THEN RIGHT(chopped.Field22,CHARINDEX('=',REVERSE(chopped.Field22))-1)
   WHEN LEFT(chopped.Field23,21) = ';1000RETURN_DELIVERY=' THEN RIGHT(chopped.Field23,CHARINDEX('=',REVERSE(chopped.Field23))-1)
   WHEN LEFT(chopped.Field24,21) = ';1000RETURN_DELIVERY=' THEN RIGHT(chopped.Field24,CHARINDEX('=',REVERSE(chopped.Field24))-1)
   WHEN LEFT(chopped.Field25,21) = ';1000RETURN_DELIVERY=' THEN RIGHT(chopped.Field25,CHARINDEX('=',REVERSE(chopped.Field25))-1)
   WHEN LEFT(chopped.Field26,21) = ';1000RETURN_DELIVERY=' THEN RIGHT(chopped.Field26,CHARINDEX('=',REVERSE(chopped.Field26))-1)
   WHEN LEFT(chopped.Field27,21) = ';1000RETURN_DELIVERY=' THEN RIGHT(chopped.Field27,CHARINDEX('=',REVERSE(chopped.Field27))-1)
   WHEN LEFT(chopped.Field28,21) = ';1000RETURN_DELIVERY=' THEN RIGHT(chopped.Field28,CHARINDEX('=',REVERSE(chopped.Field28))-1)
   WHEN LEFT(chopped.Field29,21) = ';1000RETURN_DELIVERY=' THEN RIGHT(chopped.Field29,CHARINDEX('=',REVERSE(chopped.Field29))-1)
   WHEN LEFT(chopped.Field30,21) = ';1000RETURN_DELIVERY=' THEN RIGHT(chopped.Field30,CHARINDEX('=',REVERSE(chopped.Field30))-1) END AS Return_Delivery,
   chopped.LogDateTime,
   @CaptureDate
   FROM #temp chopped;

   DROP TABLE #temp

I hope this has given a good demonstration on how to use OUTER APPLY to parse a delimited file.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s