I'm a bit confused about what you're trying to do here.
From what you say initially, it sounds like your goal is simple: You want to extract the short SKU (for example, AAAMB) from the long SKU (for example, 14AAAMBNOV72014) and then put that short SKU in another field in the same record.
If that's indeed what you want to do, I wonder:
Does that short SKU always appear in the same position within the long SKU? Based on the structure of your example long SKU, I'm guessing that might be the case.
Is the short SKU always the same length?
If so, you could simply grab the short SKU directly using Middle ( longSKU ; 3 ; 5 )
But I'm confused by your questions about searching, and they make me wonder if I've misunderstood what you're trying to achieve. And, of course, if you're SKUs aren't consistently structured, my suggestion is no help anyway.
All of the SKUS are different lengths because sometimes they have lot numbers in front of them as well. So if I just pull the SKU from the same spot it won't always be correct. Which is why I would do a search for each SKU, (such as *AAAMB*).
OK, to keep things simple, the answer is yes, you can do what you’re asking, and it's pretty much a question of which piece of the process you need help with.
Here's the broad outline:
First, you collect a list of all the short SKUs into a $variable. There are many ways to do that, but the most efficient in your case is probably the ExecuteSQL function.
Then you can go to the table you want to process and loop through that list of short SKUs in the $variable, doing this for each item on the list:
1. Perform the search
2. If your found set is greater than 0, loop through the found records adding your short SKU to the new field.
Like I said, the question now is which piece of that do you need help with.
However, I still wonder whether there isn’t a way — if we completely understood the structure of both the long SKU and the short SKU — to process each long SKU directly, even if it was just to do some pattern-matching.
Yes, for example, if lot number contain only numbers, and short SKU never contain numbers,
Left ( Substitute ( SKU ; [ 0 ; "" ] ; ..1to8 here.. ; [ 9 ; "" ] ) ; 5 )