Parse 7 different bits of data from a vary long string

[tubbers]

I'm using Crystal XI with a SQL MDB.

Does anyone have any suggestions on how to get a bunch of data parsed out of a very long string? I've gotten the easy stuff figured out but I am having difficulty getting items 3-6 since the starting point varies based on the length of the patient name and other optional information that may or may not appear.

What I need to extract is: (1) message dttm-received, (2) account ID, (3) patient name, (4) current bed location, (5) previous bed location, (6) transfer time. The example below shows the extracted information in green.

Example:
{message.rawmsg}
MSH|^~\&|HBOC|001|HBOC|001|20061024015019||ADT^A02|30889|P|2.2|||AL|NE EVN|A02|20061024014949
PID|0001||1496782|000540418|SMITHERS^MAXWELL^J||19850509|F||W||||||||1496782^^^|533-19-1695|||||||||||N
PV1|0001|I|S2^0216^1|ER||S2^0223^1|50054^SMITH, MARCUS N|||MED||||7||||I||C||||||||||||||||||||||||200610240139||0000000.
00|0000000.00|0000000.00|0000000.00 PV2||M||||||||||||||||||||N   

[b]@Determine Message DTTM[/b]
[COLOR=blue]MID[/color]({message.rawmsg},28,14)

[b]@Determine Account ID[/b]
[COLOR=blue]MID[/color]({message.rawmsg},104,7)

I had thought that if I could count the number of times the "|" appears, I could use its position as a starting point. For example, the patient name will always start after the 22nd "|" in the string. It's ending point would be the 23rd "|" in the string. Likewise, the current bed location starts at the 50th "|", previous bed location always starts at the 55th "|" and the transfer time will always start at the 91st "|".

Is there a way to do this? Or have I totally overlooked a simpler solution to this mess?

 

[lbass]

You could use something like the following:

split({table.string},"|")[7] //first datetime

split({table.string},"|")[23] //name

//etc.

-LB

 

[Turkbear]

Hi,
Just a thought that might hint at a method..

Strings can be turned into arrays using the Split function, so using

arrMyStringData = Split({message.rawmsg},"|")

would return an array with the # of elements matching the # of sections separated by the | character..So perhaps

arrMyStingData(22) would have the name..(or maybe 23)


Experimentation should allow refinement..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi lbass.
I need to type faster...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[tubbers]

thanks for the suggestions, I'll try both and see what I can come up with.

 

[tubbers]

lbass, your suggestion worked perfectly.

turkbear,
I tried your suggestion but am having a little difficulty. I've never used arrays before and the answer isn't what I was expecting.

[b]@Determine Patient Name[/b]
stringvar array RawMsg;
RawMsg = Split({Message_Queue.RawMessage},"|")[22]

When I put this formula in the details section, I get the answer "false". I changed the position to 23 and still get the same answer.