Grab OrderID

Question

Grab OrderID

adonetok

SSCrazy Eights

Points: 8395
More actions
May 12, 2015 at 10:57 am

#303741

There a lot of OrderID within a big text file.
The OrderID format is like below:(OrderID is 16 characters after ADR*XYZ*)
(some other characters)ADR*XYZ*2015123456BH0001~(and some other characters)
(some other characters)ADR*XYZ*2015113955AB0002~(and some other characters)
There are a few characters like "~", "-" after OrderID and before "ADR*XYZ*"
How to code to read only 16 characters after "ADR*XYZ*" and insert them into a table?
File is something like below. OrderID are located anywhere in the file.
O****MI*16659434~TRN*2*A257330~STC*~ADR*XYZ*2015123456BH0001~REF*BLT*831~DTP*472*RD8*20141216-20141216~HL*8*3*PT~NM1*QC*1*CRUZ*ILDEMARO****MI*ADR32624R01~TRN*2*A23~ADR*XYZ*2015113955AB0002~*A2:20*20150506*WQ*

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 1

Does the pattern [ADR*XYZ*] always hold?

If so, when you put this in a table, you can always use PATINDEX/CHARINDEX to find the beginning and offset to the beginning of your orderID with SUBSTRING.

If the ~ is always at the end, you can start a second PATINDEX/CHARINDEX search after the place where you find the first one and use that to determine the end, hence, the length for your substring.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Steve Jones - SSC Editor (5/12/2015)
If the ~ is always at the end, you can start a second PATINDEX/CHARINDEX search after the place where you find the first one and use that to determine the end, hence, the length for your substring.

Or use 16 if the length is fixed.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 3

Is this what you're looking for?

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp

GO

CREATE TABLE #temp (

BigNastyString VARCHAR(255)

)

INSERT #temp (BigNastyString) VALUES

('some other characters)ADR*XYZ*2015123456BH0001~(and some other characters'),

('some other characters)ADR*XYZ*2015113955AB0002~(and some other characters'),

('O****MI*16659434~TRN*2*A257330~STC*~ADR*XYZ*2015123456BH0001~REF*BLT*831~DTP*472*RD8*20141216-20141216~HL*8*3*PT~NM1*QC*1*CRUZ*ILDEMARO****MI*ADR32624R01~TRN*2*A23~ADR*XYZ*2015113955AB0002~*A2:20*20150506*WQ* ')

SELECT

SUBSTRING(t.BigNastyString, PATINDEX('%ADR*XYZ*%', t.BigNastyString) +8, 16) AS OrderID

FROM

#temp t

Results...

OrderID

----------------

2015123456BH0001

2015113955AB0002

2015123456BH0001