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;
Just related to your initial question I would probably something like this:
load recno() as RecNo, rowno() as RowNo, iterno() as IterNo, BOMCost_Value, subfield(BOMCost_Value, 'VALUE_ENTERED', iterno()) as FieldPart resident BOMCost while iterno() <= substringcount(BOMCost_Value, 'VALUE_ENTERED') +1;
whereby the split-loop comes from the bold part.
If I look on your data-example I would rather try to transform the whole string into a normal data-structur. This means using the above mentioned approach multiple times on top of each other, like:
load *, subfield(F3, ',', iterno()) as F4, iterno() as IterNo3 while iterno() <= substringcount(F3, ',') + 1; load *, subfield(F2, ':', iterno()) as F3, iterno() as IterNo2 while iterno() <= substringcount(F2, ':') + 1; load F, subfield(F, ';', iterno()) as F2, iterno() as IterNo1, recno() as RecNo from Source while iterno() <= substringcount(F, ';') + 1;
If your field is reasonably created you won't not have too much difficulties to specify the appropriate delimiters to divide the multiple parts. Even if not there are often enough information included to find and/or replace the delimiter maybe by counting the number and positions of the different delimiters and the lengths of the parts and also using additionally more string-functions like textbetween(), mapsubstring() and so on. By including the shown recno/rowno/iterno it's very easy to comprehend which information comes from where and which adjustments return in which results. Just play a bit with it.
Much easier that this would be if you could connect to the source-database and not using such output ...
It sounds like that there should be an easier way to that, may be using in combination with another string function. Could you please give a sample string and expected output in that context? - Let us try.
Two instances of substrings - that I got, but then how are you deciding the other parts of the strings in the final output? May be with a simpler example, like:
'AAXXX bbbb MMMM KKKKK TTTTKLMP sggsgsg 123;1233333; TTTTTpoltttaa&@' - is my string and we have two 'TTTT's in it, then we have to find two substrings from the entire long string. What is the logic for defining these two substrings? do you have a delimiter or separator?
Just related to your initial question I would probably something like this:
load recno() as RecNo, rowno() as RowNo, iterno() as IterNo, BOMCost_Value, subfield(BOMCost_Value, 'VALUE_ENTERED', iterno()) as FieldPart resident BOMCost while iterno() <= substringcount(BOMCost_Value, 'VALUE_ENTERED') +1;
whereby the split-loop comes from the bold part.
If I look on your data-example I would rather try to transform the whole string into a normal data-structur. This means using the above mentioned approach multiple times on top of each other, like:
load *, subfield(F3, ',', iterno()) as F4, iterno() as IterNo3 while iterno() <= substringcount(F3, ',') + 1; load *, subfield(F2, ':', iterno()) as F3, iterno() as IterNo2 while iterno() <= substringcount(F2, ':') + 1; load F, subfield(F, ';', iterno()) as F2, iterno() as IterNo1, recno() as RecNo from Source while iterno() <= substringcount(F, ';') + 1;
If your field is reasonably created you won't not have too much difficulties to specify the appropriate delimiters to divide the multiple parts. Even if not there are often enough information included to find and/or replace the delimiter maybe by counting the number and positions of the different delimiters and the lengths of the parts and also using additionally more string-functions like textbetween(), mapsubstring() and so on. By including the shown recno/rowno/iterno it's very easy to comprehend which information comes from where and which adjustments return in which results. Just play a bit with it.
Much easier that this would be if you could connect to the source-database and not using such output ...
