Remove everything before last _ in given field name

I have a number of fields that were joined together, and thus renamed with various "left_" "right_" before the field name.

e.g.

_left_left_right_field 1

_left_right_field 2

_left_right_right_field 3

what I would like to do, possibly with the combination of dynamic rename and regex, is remove anything prior to and including the last _ in any given field header.

e.g.

field 1

field 2

field 3

Regex

Accepted answers

Thableaus

Hi @IJH34

Here's a good regex:

REGEX_REPLACE([FieldName], ".*_(.*)", "$1")

Cheers,

All comments

LordNeilLord

Hey @IJH34

Not sure its the most efficient method but I like to use reversestring...

ReverseString(Left(ReverseString([Field1]), FindString(ReverseString(Field1), "_")))

Neil

Thableaus

Hi @IJH34

Here's a good regex:

REGEX_REPLACE([FieldName], ".*_(.*)", "$1")

Cheers,

danilang

And another one

REGEX_Replace([Field1], "(.*_)+", "")

Dan

IJH34

@Thableaus Perfect! Regex is a tricky one for me.

MarqueeCrew

@IJH34,

Back in the day before I knew what RegEx was, I would solve this type of problem with string functions. While not as fancy as pattern matching, it runs faster. In fact, with 30 million records it takes 48.4 seconds to calculate your results using RegEx (quite fast!) and only 37.5 seconds to use the following formula:

Right( 
     [Field1], MIN(
FindString(ReverseString([Field1]), "_thgir"),
FindString(ReverseString([Field1]), "_tfel")
     )
)

Bottom line is that you should decide which solution is more easily explained to your team and use RegEx when you're comfortable with it. I do encourage you to learn it. @danilang and @Thableaus gave great RegEx advice and @LordNeilLord sent you down the same path that I would have taken you on. All of the suggestions in the post are solution worthy.

Cheers,

Mark

LordNeilLord

I totally agree @MarqueeCrew

If you don't understand it....don't use it (or learn it quickly)

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335