Oracle Analytics and AI

User_JL3CW

HI,

Please let me know how to get the following substring in a string.

Cost Center.Currency Gains & Losses.Default.Product.Customer.Varying values

I have to get the Currency Gains & Losses substring  in the above string. Please let me know how to achieve this.

regards,

ram.

Gianni Ceresa

Hi,

If you open the formula edit window in your analysis you will see a bunch of String functions available.

In your case you maybe want to look at LOCATE, to find position of 1st " . " in the string. So something like LOCATE('.', "your table"."your column").

The same function can accept a 3rd parameter saying from which character to start searching for a " . ".

So if you use the same function by passing as 3rd parameter the position of the 1st " . " +1 you get the position of the 2nd " . ".

Finally another function is called SUBSTRING ...

Put all that together and you get your result.

That's the "how" to get there.

From a more practical point of view you maybe want to get that in your source data directly, so maybe by extracting the info into a column by ETL.