Solved SQL copies data with indent

[murray83]

ok another similar question

how would i chop the pdf when pasted into raw data so it got the address

Sales Order
From:
DHL SUPPLY CHAIN
DANES WAY
NORTHAMPTONSHIRE
CRICK NN6 7EX
UNITED KINGDOM
Ship To:
BEST FOOD LOGISTICS (PRET A MANGER)
RATTY'S LANE
HODDESDON EN11 0RF
UNITED KINGDOM
Order Number:
Customer Number:
Customer Ship To:
Customer Order No:
Due Date:
Booking Time:
Booking Ref:
ON00384524
C000794
1
349792
12/06/2025
22.00 -
408913
Line Customer SKU HS Product Code Line Weight Qty UM Pallets
1 PMA984213 120750015PSSC PRET 75CL STILL SPORTS 20,857.20 2,184 CA 26.00
customer.logistics@highlandspringgroup.com;evcs.highlandspring@evcargo.com;gaylord.mutambiranwa2@dhl.com;gordon.eyles@dhl.com;jayce.baldwin@DHL.com;M.Carpenter@dhl.com;mark.simmonds@dhl.com;mark.trueman@dhl.com;michael.dunham@dhl.com;philibert.nsaata@dhl.com;TrafficControl@Highlandspringgroup.com
Please pick the attached order ready for collection. Contact customer.logistics@highlandspringgroup.com with any issues.
all weights specified in kilograms
Total Qty:
Total Qty Weight:
Total Pallets:
Total Pallet Weight:
Total Overall Weight:
2,184.00
20,857.20
26.00
728.00
21,585.20
If this order is delivered incorrectly please contact Highland Spring Group Customer Support on 01764 660 525
HAULIER INFORMATION / RECEIVED IN GOOD CONDITION
Driver's Signature:
Reg Number:
Trailer Number:
Date Collected:
No. of Straps:
No. of Straps Used:
RECEIVED IN GOOD CONDITION
Signed:
Date:
Time:

As i thought about SQL but then that wouldnt work as not all address details are fixed length, so would need to chop out the post code at the least and then i could use that as a join as i have all address used in anotehr table

 

[Gasman]

I would be reading the data as a text file.
Then find From: or ShipTo: and read until next item.

 

[murray83]

SELECT
    MID([RawData],
        INSTR(INSTR([RawData], 'UNITED KINGDOM') + 1, [RawData], 'UNITED KINGDOM') - 10,
        8) AS ShipToPostCode
FROM TempRawData
WHERE TempRawData.RawData Is Not Null;

 

[Gasman]

One postcode covers multiple addresses?
What are you going to do for postcodes like Birmingham?

 

[spaLOGICng]

It appears there is an embedded carriage return or line feed even after removing the Booking Ref:

You can use the Replace() function to remove it/them


Replace(Replace(Replace(Mid([RawData], InStr([RawData], "Booking Ref:") + 12, 12) AS BookingRef,CHR(9),""),CHR(10),""),CHR(13),"")

You may want to scrub your raw data before it ever gets to this point. You can do it in an underlying query and do it all at once. You may need to adjust your MID() positions and lengths