Let's Reconnect, thank you for making 2022 our best year yet.

Look Back
OTBI - Locating 5th occurrence of character using substring and locate — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

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

Accepted answer
47
Views
9
Comments
edited Apr 12, 2021 2:23PM in Reporting and Analytics for HCM 9 comments

Summary

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

Content

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.

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!