Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved]Extract a series of substring after particular delimiter in whole string

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved]Extract a series of substring after particular delimiter in whole string
 Login/Join
 
Gold member
posted
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,


WebFocus Version 7.7.05
Windows, HTML/PDF/EXL2K/AHTML
 
Posts: 63 | Registered: January 12, 2011Report This Post
Guru
posted Hide Post
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,


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
Use GETTOK


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
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!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
Thank you, Daniel..Your code resolves the issue.


WebFocus Version 7.7.05
Windows, HTML/PDF/EXL2K/AHTML
 
Posts: 63 | Registered: January 12, 2011Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved]Extract a series of substring after particular delimiter in whole string

Copyright © 1996-2020 Information Builders