Extracting Vertical, Program & Process from Department Field via FDI Formula - at Talent Acuqisition

Question

Subject Area: HCM Talent Acquisition

In my “Department” field I have hierarchical values separated by underscores (“_”), for example:
IN_PS_009051_Customer Operations_Voice AM Pune_Comcast Inbound Pune Training

In this string:

* Vertical = Customer Operations (3rd segment)
* Program = Voice AM Pune (4th segment)
* Process = Comcast Inbound Pune Training (5th segment)

Using FDI formulas, how can I:

* Extract the 3rd segment and assign it to a “Vertical” field,
* Extract the 4th segment and assign it to a “Program” field, and
* Extract the 5th segment (if present) for “Process”?

What is the correct syntax in Oracle FDI to split a string by “_” and reference the 3rd, 4th and 5th parts?

Jestin Rajan · Answer

Hi, not sure if FDI supports any formula to perform this action, but I would have used Dataflows, which has a Split transformation function. It allows you to split values into columns using “_” as a delimiter and then keep only the required columns by renaming them.