I think the first recommendation would be to not let them put job numbers into the notes field. I would create a separate JobNumber Table, and put a small portal with something like:
Job Number-Start Job Number-End
Start Field End Field
Then if they just enter a number in the start field, and leave the end field blank, you could auto enter the start field number. If they put numbers in each you can make sure the Start field is 'lower' then the End field.
As far as parsing, is it always two dashes (--)?
And is it always separated by a comma? What if they use a colon, semi-colon, period or blank space?
We don't have a choice on where to put the numbers. We're using the FedEx and UPS shipping programs and exporting csv files at the end of the day. The solution I'm working on takes those csv files, combines the FedEx and UPS records into a third table, parses out the records then updates the "Jobs" database.
It is usually two dashes, sometimes one for a range. They always use a comma or a space for the non-sequential numbers.
I have the script so that it changes commas, spaces, colons, semicolons or periods into carriage returns. Then it looks for -- and parses out the range by entering each number separated by a carriage return. Then does the same thing for single dashes.
The one place it breaks is when there are two sets of ranges in the field. Even though the script first puts the carriage return between the two ranges it still looks at the first word and last word as the beginning and end point for the range.
For now I put in a If patterncount (jobnumber; "--") > 1 = "mark as an error" so I can manually go fix that record later.