How to Return the nth matching pattern in Business Objects Web Intelligence 4.2

Question

I have created the following variables in Business Objects 4.2:

2cPat =Pos([Service Item Value];"2C" )

2C Ref #1 =If([2cPat])=0 Then "" Else (Substr([Service Item Value];[2cPat] ;10 ))

what the 2cPat variable do is looks on the [Service Item Value] column row by row and finds the 1st word that starts with 2C

What the 2C Ref #1 variable does is extracts the matched pattern that starts with 2C and is 10 characters in total length.

The problem is that I can have more than 1 2C match per row and I need to extract all the ones that appear on each row.

My approach will be to create another variable called 2C Ref #2but the problem that I have is how do I make the code ignore the already found 2C that has been stored on 2C Ref #1.

Here is a sample of how the data looks right now:

Service Item Value                                                                                                        2C Ref #1    2C Ref #2    2C Ref #3    2C Ref #4    2C Ref #5
----------------------------------------------------------------------------------------------------------------------    ----------   ----------   ----------   ----------   ----------
E2E: 2C03144250 ;    Site 1: 2C03144220; Site 2: 2C03144245  Site 2: EAFE03144169  C72/273918                             2C03144250   2C03144220
Site 1: 2C03100386; Site 2: 2C03100440 ; End to End: 2C03100444  SITE1: EAGE02897836 ; SITE2: EAFE03100362  C72/266267    2C03100386   2C03100440           
ETHWIR000214950, e2e 2C02474833 , 2C02474832 POP - 2C02474831 CX                                                          2C02474833   2C02474832

Because of the length of the data sample I cannot make it fit properly here on Stack Overflow but please also see a screenshot that I have taken:

Answer 1

I have tried and have succeeded but to extract the whole string you need write many formula as I couldn't find any loops functionality in WEBI.

Here goes the solution:

First extract the first 2c string and then store the remaining string in new variable and in step 2 extract the successive 2c string and then store the remaining in another variable this way you need to go till the end of string later if you need you can concatenate the all individual 2c strings.

Formula for first 2c string

=Substr([Column1];Pos([Column1];"2C");10)

Now extract the remaining string other than this string:

Remaining string excluding the forst 2c part "remainining formula:

=Right([Column1];Length([Column1])-(Pos([Column1];"2C")+10))

Now extract the next 2c value from remaining string

=Substr([remaining];Pos([remaining];"2C");10)

Now you need to extract the next part of the string excluding above 2c values.

Attaching the screenshot of what I have done.

Try and let me know