Finding, then extracting text string of a particular format

[addy]

Hi

I have a large approx. 400,000 records which have a text field of up to 250 characters in length. I would like to search for a text string of a particular format, for example, LLL-NNNNN where L is any letter and N is any number and if found, I would like to extract the particular string from the text.

Searching for all records which contain a text string which contains this format isn't too tricky I believe, but how do I then say OK, extract that block of text? I'm assuming I need to use the Mid([mytextstring],X,9) (9 from the example above) but how do I set the value of X?

I'm having a bit of a brain dead day and can't figure it out

Thanks.

 

[lameid]

To limit results that contain that text I would use the like operator... Unfortunately I don't use the leter and number masks often enough to remember them cold but you should not have a problem finding them in help.

From there, the only thing I can think to do is use a combination of instr, mid, and like to test all the values surrounding a dash... If the format matches it is your value, otherwise find the next one (increment the start value of instr). You might even use the ISNUMERIC function for the number portion.

I hope that is enough to get you going... post back with your results.

 

[SkipVought]

what character DELIMITS this pattern? Is it a SPACE or what?

Or is it just burried like this...
[tt]
123xc-123abc-1234nyc-12345zwx-1234
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]