Hi,
I need to extract few series of substring after Comma (,) delimiter. I tried various options, but able to get only first series from the entire string.

Below is the sample code:

 
DEFINE FILE CAR
TEST/A500='Includes: 000,AAA - Account test1,BBB - Account test12,CCC - Account test123,DDD,EEE - Account test12345';
END
TABLE FILE CAR
PRINT
COUNTRY 
TEST
WHERE COUNTRY EQ 'ENGLAND'
END

I need the first three characters from the string after each Comma (,) delimiter is encountered as shown above the expecting result

This message has been edited. Last edited by: SriAravind,

FIELD2/An=SUBSTR(len1,field1,start,end,len2,format); .................................................... extract string from FIELD1
FIELD2/An=SUBSTR(field1,start,len2); ............................................................................. extract string from FIELD1
len1: ................................................................................................................... integer length of FIELD1
field1: ................................................................................... alphanumeric fieldname or 'literal' to be checked
start: ................................................................................................ extract starting at this integer position
end: ........................................................................................................ stop extracting at this integer position
len2: ....................................................................................................................... output string length
FIELD2: ............................................................................................................ output string of length len2
NM format: 'An' .................................................................................................... specify the receiving format
format: fieldname ...................................................................... specify a fieldname having the required format

FIELD2/An[V] =SUBSTRV(field1,start,len2); .................................................................. extract string from FIELD1
len1: ....................................................................... upper limit to integer length of FIELD1, in An or AnV format
field1: ................................................................................... alphanumeric fieldname or 'literal' to be checked
start: ................................................................................................ extract starting at this integer position
end: ................................................................................................... stop extracting at this integer position
len2: ........................................................................................................ upper limit to output string length
FIELD2: ............................................................................................................. output string of length len2
NM format: 'An' or 'AnV' ........................................................................................... specify the receiving format
format: fieldname ...................................................................... specify a fieldname having the required format


Source: WebFOCUS KEYSHEET from Aviter.

This message has been edited. Last edited by: Ricardo Augusto,

Use GETTOK

Sri,
Your problem has many aspects.
1. For each occurence of your string, you do not know how many commas there are.
2. In what you show there is also the substring from the beginning until the comma.
3. Your example shows that you always take 3 characters after the comma.

I assume that points 2 and 3 are mandatory. One also has to assume what is the max length of the beginning, the max length of the substring between commas, the max length of the result. I used 20 for the first, 100 for the second, 150 for the result.

Example:

  
-* File sri01.fex
-* Create data
DEFINE FILE CAR
TEST/A500=DECODE COUNTRY(
ENGLAND 'Includes: 000,AAA - Account test1,BBB - Account test12,CCC - Account test123,DDD,EEE - Account test12345'
FRANCE  'Contient: 111,WWW - Account test1,XXX - Account test12,ZZZ,QQQ'
ITALY   'Comprende: 222,III,JJJ'
JAPAN   'Mushimushi: 333,*** - Account test_***,LLL - Get them,MMM - GOOD,PPP'
ELSE    'Nothing: 999,EEE,FFF');
END
TABLE FILE CAR
PRINT TEST
BY COUNTRY
ON TABLE HOLD AS SRI
END
-RUN
-* Find the max number of commas
DEFINE FILE SRI
TESTNOCOMMAS/A500=STRIP(500, TEST, ',', 'A500');
TESTLEN/I5=ARGLEN(500, TEST, 'I5');
NOCOMMASLEN/I5=ARGLEN(500, TESTNOCOMMAS, 'I5');
COMMAS/I3=TESTLEN - NOCOMMASLEN;
END
TABLE FILE SRI
SUM MAX.COMMAS
ON TABLE SAVE
END
-RUN
-READ SAVE,&MAXCOMMAS
-RUN
-*
DEFINE FILE SRI
-* Extract the beginning
TPARM/A20=GETTOK(TEST, 500, 1, ',', 20, 'A20');
-* Create fields for each substring (some might be empty). Then extract the first 3 characters.
-REPEAT #PARMS FOR &I FROM 1 TO &MAXCOMMAS;
-SET &J=&I+1;
STR&I/A100=GETTOK(TEST, 500, &J, ',', 100, 'A100');
PARM&I/A3=EDIT(STR&I, '999');
-#PARMS
-* Concatenate all the extracted pieces (some might be empty) and separate them with commas.
SRI/A150=TPARM
-REPEAT #GPARMS FOR &I FROM 1 TO &MAXCOMMAS;
|| ',' || PARM&I
-#GPARMS
;
-* Since some pieces might be empty, there will be some commas at the end of the string. Use TRIM to erase them:
TSRI/A150=LJUST(150, (TRIM('T', RJUST(150, SRI, 'A150'), 150, ',', 1, 'A150')), 'A150') ;
END
-RUN
-* Output:
TABLE FILE SRI
PRINT TSRI
BY COUNTRY
END

Good Luck!

Thank you, Daniel..Your code resolves the issue.