I have a table with a long text string value, 'BOMCost_Value', I am searching the text string for particular text, to extract text after it, and the number of times it occurs. I want to be able to run a for..next loop to pull out all occurrences. I have done this with the code below but it not very elegant when I have up to 27 occurrences.
BOMCost1:
LOAD
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED')+16,60) as cost_long1,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',2)+16,60) as cost_long2,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',3)+16,60) as cost_long3,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',4)+16,60) as cost_long4,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',5)+16,60) as cost_long5,
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',6)+16,60) as cost_long6
Resident BOMCost;
I tried the following but 'mycounter' isn't available within the load.
Note : 'cost_count1' is the number of occurrences of the text within the string, which I have within the original BOMCost table.
for mycounter=1 to $(cost_count1);
set mycostid='cost_long' & mycounter;
BOMCost1:
Load
mid(BOMCost_Value,Index(BOMCost_Value,'VALUE_ENTERED',mycounter)+16,60) as mycostid
Resident BOMCost;
next;
