Using SUBSTRING and LOCATE for Powerful Data Parsing

2 November 2024

Using SUBSTRING and LOCATE for Powerful Data Parsing

SUBSTRING and LOCATE in OTBI

In Oracle OTBI, key flexfields and codes are often stored as concatenated strings separated by dots or other delimiters. Extracting individual segments from these composite fields is essential for flexible reporting and this is where the SUBSTRING and LOCATE functions become invaluable.

Why Use SUBSTRING and LOCATE in OTBI?

Key flexfields such as “GL Account.Concatenated Segments” or “Worker Assignment Details.People Group” often combine several coded segments separated by dots (.). To isolate a particular segment, you must dynamically find delimiter positions and extract just what you need, regardless of variable segment lengths.

How Does It Work? Practical Examples

1. Extracting the First Segment

Suppose you want everything before the first . in "GL Account"."Concatenated Segments":

				
					SUBSTRING(
  "GL Account"."Concatenated Segments"
  FROM 0 FOR 
    (LOCATE('.', "GL Account"."Concatenated Segments")) - 1
)

This extracts all the characters up to but not including the first dot.

2. Extracting the Second Dot-Delimited Segment

Now, let’s say you want the segment after the first dot, up to the second dot, from "Worker Assignment Details"."People Group". The logic chains two LOCATE calls one to find the start, another for the end:

				
					SUBSTRING(
  "Worker Assignment Details"."People Group"
  FROM LOCATE('.', "Worker Assignment Details"."People Group") + 1
  FOR
    LOCATE('.', "Worker Assignment Details"."People Group", LOCATE('.', "Worker Assignment Details"."People Group") + 1)
    - LOCATE('.', "Worker Assignment Details"."People Group") - 1
)

This extracts the substring between the first and second dots, correctly handling variable lengths.

3. Extracting Deeper Segments in Variable-Length Codes

To extract, for example, the third or fourth segment—or any segment in a complex string—you can nest LOCATE calls to discover nth occurrences:

				
					SUBSTRING(
  "Field"
  FROM LOCATE('.', "Field", LOCATE('.', "Field", LOCATE('.', "Field") + 1) + 1) + 1
  FOR LOCATE('.', "Field", LOCATE('.', "Field", LOCATE('.', "Field") + 1) + 1)
    - (LOCATE('.', "Field", LOCATE('.', "Field") + 1) + 1)
)

This formula can be extended to target any segment by incrementing the nesting of LOCATE accordingly.

Best Practices

Always test your expression with real data, as presence/absence of delimiters or nulls can alter results.
For segments that might not exist, consider wrapping with conditional logic (e.g., CASE) to catch errors or blank outputs.
These functions can be chained creatively for parsing IDs, lists, or composite fields in any OTBI subject area.

Summary

Using SUBSTRING and LOCATE together allows you to slice and dice key flexfields and any delimited string in OTBI analyses, making complex and customised reporting much easier.

Need some assistance?

Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.