Trim multiple times per field

[unknownb]

Hello,

I'm currently trimming the first 63 characters of a field using the below expression to eliminate non required data.

download.cfm?file=XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXXX&filename=XXXXXXXXX

Right(field, Len(field)-63)

To leave

XXXXXXXXX

However, the data, sometimes, if there has been multiple files submitted in this instance, this will concate like the below.

download.cfm?file=XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXXX&filename=XXXXXXXXX,download.cfm?file=XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXXX&filename=XXXXXXXXX

I need to remove download.cfm?file=XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXXX&filename=XXXXXXXXX each time it is present in a field to leave just the filename in the instance. 

XXXX changes per every file upload.

Is this possible?

[marcus_sommer]

You may use subfield() with two parameters or alternatively with three parameter by using while with iterno() to split the string into multiple records and then applying your original trimming.

Further possible may be to use subfield() with three parameters and/or textbetween() and various string-functions like substringcount(), index(), keep/purgechar() as well as the classical string-functions like left/mid/right() and so on to extract your wanted content.

- Marcus 

[NitinK7]

I am not sure 

but you can try filebasename() at script level

[unknownb]

Thanks for the replies so far, is there anyway to do this not a script level and in app.

Can you advise how the subfield() would work in this instance?

[marcus_sommer]

You could use subfield() with 3 parameters, textbeween() and all the other mentioned string-functions within the UI, too.

But you couldn't apply any kind of loop-processing else the transformation needs to be done always and completely against the origin string-value. Further if the result shouldn't be only displayed as a measure you creates with such approach a calculated dimension which is evaluated again and again against the dataset and needs more resources as native dimensions.

Further disadvantages could occur within the display of the current selection box and by the needs to use them within aggr() or TOTAL statements.

In short: nothing is speaking for doing such transformation within the UI else the recommended way would be to do it within the script.

- Marcus