Trim last part of text field with variable lengths

[#1]

Hello,

I am trying to cut the last part of the field that is the last instance of an underscore ("_"). For instance, I have a field with the 2 values below:

1. CREDIT_ONLY_LAX
2. RET_LINE_WITH_CREDIT_LS_GB

I want to cut the "_LAX" from the first example and the "_GB" from the second example. The result would then be:

1. CREDIT_ONLY
2. RET_LINE_WITH_CREDIT_LS

Any help would be great. My difficulty here is that it cannot be hard coded such as left(field, len -3) as it could range from 2-5 characters that I'm trying to cut off.

Thanks!

[marcus_sommer · Accepted solution]

Try: left(Field, index(Field, '_', - 1) - 1)

- Marcus

[sunny_talwar]

Try this:

Table:

LOAD Left(Field, (Len(Field) - Len(SubField(Field, '_', (Len(KeepChar(Field, '_')) + 1))) - 1)) as [New Field],

  Field;

LOAD * Inline

[Field

CREDIT_ONLY_LAX

RET_LINE_WITH_CREDIT_LS_GB

];

[#4]

Thank you to both!