Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Reg: Substring query

Received Response
1
Views
1
Comments
User_JL3CW
User_JL3CW Rank 6 - Analytics Lead

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.

Answers

  • 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.