How to find multiple occurrence of a string in a field?

[apoorvasd]

Hello Everyone,

I have a field which has values as shown below. I am trying to fetch the string which is between, '"href="' and '"><img"', i.e., the path of a particular file. But this string can contain more than one "href" value in a single line as shown in below picture. So how can I manage to get all strings between "href" and "img" here?

When a particular value in this "link" field has only one occurrence of "href", i can use subfield function to get the string. But the problem is when it has more that one "href" value in the same line as shown in second picture above.

Any help on this?

Thank you.

[sunny_talwar]

If the occurrence is twice, do you want to pull it twice or do you need 1st or 2nd occurrence only?

[apoorvasd]

If the occurrence is twice, i would want to pull it twice. Similarly, if it is thrice, i would want to pull it thrice and so on.

Thank you.

[sunny_talwar]

Pull them twice or thrice in the same row or multiple rows?

[apoorvasd]

May be same row

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD RowNum,

Concat(Field, ',') as Field

Group By RowNum;

LOAD RecNo() as RowNum,

TextBetween(Field, 'href="', '"><img', IterNo()) as Field

While IterNo() <= SubStringCount(Field, 'href="');

LOAD * INLINE [

    Field

    'abc href="adhsdbsh"><img'

    'hdakddhsjd href=" admvfjsjf"><img ahdhsjdhs href=" alkfsmfh "><img gfhsghfg href="adaaaaaa"><img ahdgsahd'

];

In case you need this in multiple rows... try this

Table:

LOAD RecNo() as RowNum,

TextBetween(Field, 'href="', '"><img', IterNo()) as Field

While IterNo() <= SubStringCount(Field, 'href="');

LOAD * INLINE [

    Field

    'abc href="adhsdbsh"><img'

    'hdakddhsjd href=" admvfjsjf"><img ahdhsjdhs href=" alkfsmfh "><img gfhsghfg href="adaaaaaa"><img ahdgsahd'

];

[apoorvasd]

Hi Sunny,

Thank you very much! This was what i wanted. But, there are other fields in my table along with this, when i implement this code, other fields are not being pulled. I see only fields, "RowNum" and "Field". Can you please help me here?

Thank you.

[sunny_talwar]

Sure, can you share the script you tried running?

[apoorvasd]

PFA!

I have added an additional field named as "id" to your script in inline table. When i reload this app, i am not able to find this field.

[sunny_talwar]

Unless you add id to the preceding loads, not sure how you expect it to show up after the reload is completed

Table:

LOAD RowNum,

id,

Concat(Field, ',') as Field

Group By RowNum, id;

LOAD RecNo() as RowNum,

id,

TextBetween(Field, 'href="', '"><img', IterNo()) as Field

While IterNo() <= SubStringCount(Field, 'href="');

LOAD * INLINE [

    Field, id

    'abc href="adhsdbsh"><img', 1

    'hdakddhsjd href=" admvfjsjf"><img ahdhsjdhs href=" alkfsmfh "><img gfhsghfg href="adaaaaaa"><img ahdgsahd', 2

];