matching string of text and extracting into new field

[bchi99]

Hi, I have a field called "contents" in two different tables that fetch the body of email messages. One table is called test1 the other test2. I need to write a query that will match a string of text that shows up in the middle of both of the fields that look like this:

[test1].contents looks like
File List:
13495234978423usa.zip

Login


------------And---------

[test2].contents looks like:

File List:
13495234978423usa.zip
Total(no extra space)

I need help extracting just the “13495234978423usa.zip” string of text to put into a new field called extracted_text in each of their respective tables.

Any takers?

[June7]

Are you saying the field [contents] contains the actual string "Field List:" followed by a CrLf in every record?

The part you want will always by 21 characters?

[bchi99]

The contents field will always contain "File List:" followed by a CrLf in every record. The filename i'm trying to extract isn't always 21 characters long though.

[June7]

That does complicate.

I forgot to ask if "File List:" is always the beginning and a CrLf follows the file name? Is it always .zip? Is that the only dot in the string?

x represents the field:

Mid(x, InStr(x,Chr(10))+1, InStr(x,".") - 9)

Consistency is critical in string manipulation.

[bchi99]

"File List:" is always in the middle of the contents and is always followed by CrLf. It can either be .zip, .rar, .doc,.pdf.

[June7]

Okay, is that the first CrLf? Is that the only dot in the string? Is that the only colon? All extensions are 3 characters?

Mid(x, InStr(x,":")+3, InStrRev(x,Chr(13)) - InStr(x,":")-3)

or

Mid(x, InStr(x,Chr(10))+1, InStrRev(x,Chr(13)) - InStr(x,Chr(10))-1)

[bchi99]

all extensions are 3 characters but there are several colons and several CfLf before this line.

[June7]

Mid(x, InStr(x,"File List:")+13, InStrRev(x,Chr(13)) - InStr(x,"File List:")-13)

[bchi99]

that worked. could you please help me using it with a update query?

[bchi99]

Nevermind. thanks so much!!!!

[bchi99]

So, i didn't realize i didn't have the field expanded. Your solution starts with the file name but it doesn't end at the file extension. It extracted all the message after the file name. Any suggestions?

[June7]

It works for me with the example data provided.

As I said, consistency in structure is critical to text manipulation. I can only work with info provided.

[bchi99]

better yet, can't i run another update query that will remove everything after the first CfLf

[June7]

Yes. But it would be nice to do all in one action. Need better understanding of data structure.

Is this a one-time event or will you need to do this periodically?

[bchi99]

It's just a one time event.