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;
