OTBI - Locating 5th occurrence of character using substring and locate

Apr 12, 2021 2:23PM

Summary

Several fields have been migrated into one field and need to split into separate columns into OTBI

Several fields have been migrated across as one field separated by a "."

I am trying to pick up the 5th occurrence of "." in the below field and not display any data after this field.

For example in the below example I need to extract "09"

PTFA..UNIDAYS.01.Yes.09.2021.Admin.No.

The below picks up everything from the 5th full stop (period) onwards, but I just want to display the two digits.

This field is also not always populated so could end up being empty.