Trim Characters

[evansabres]

Hello -

I have a field called Order

The records in that field vary in length but end in _FY15. (The field also starts with XXX_)

How can I trim the _FY15 keeping in mind that each ad name has a different number of characters?

I already have removed the XXX_ by doing the following in my script:

Order,
RIGHT([Order],  Len([Order]) -4) AS orderSHRTNME,

This has allowed me to remove the XXX_, but now I want to remove the _FY15.

Thank you.

[sunny_talwar]

There are couple of ways

1) Replace(Order, '_FY15', '')

2) Mid(Order, 5, Len(yourFieldName) - 4) //Assuming you still have XXX_ in your field I am starting from 5

HTH

Best,

Sunny

[evansabres]

Is this one line in the script or two? Not working for me

[sunny_talwar]

In the script like this:

Mid(Order, 5, Len(Order) - 4) as orderSHRTNME

or

Replace(Replace(Order, 'XXX_', ''), '_FY15', '') as orderSHRTNME

[sunny_talwar · Accepted solution]

My bad:

Try this:

Table:

LOAD Order,

  Mid(Order, 5, (Len(Order) - 9)) as OrderShortName;

LOAD * INLINE [

    Order

    XXX_123_FY15

    XXX_345_FY15

];

Best,

Sunny

[evansabres]

This worked beautifully. Thank you.

[sunny_talwar]

No problem, sorry for the confusion earlier. Did not do a test, just posted it from my memory. the second input for Mid is not the end point, but number of characters from the starting point to include.

HTH

Best,

Sunny