OBIEE extract string from between 2 characters

Joe Choueiri-Oracle

Need your help please.

I need to extract string from between 2 characters in obiee

For example if I have a text in a column that says "Original Order *IN 194186* / Product Purchased: Unlimited Learning Subscription (12 months) / Quantity: 22 / Agreement used: EDU-IN-12145971-31-MAY-2015 / Subscription set to expire on 13-AUG-16"

and I want to extract only the letters and numbers that are between the 2 asterisk (*) and these characters and number could be in the middle of the string or in the beginning or at the end.

I got to this point where this

TRIM(BOTH '*' FROM SUBSTRING( "Opportunity Attributes"."Miscellaneous Text" from Locate('*',"Opportunity Attributes"."Miscellaneous Text")+1  for LOCATE('*', "Opportunity Attributes"."Miscellaneous Text",-1)-1))

Is producing this:

IN 194186* / Product Purc

Thanks

Joe

Christian Berg-0racle

Before even answering: Why do you want to do this in the front-end?

Joe Choueiri-Oracle

Hi Christian

I do not have access to the back end, our team has only access to the front end UI but not the RPD or anything else.

Joe

Gianni Ceresa

SUBSTRING doesn't take a FROM position and a TO position, but takes a FROM position and FOR number of characters.

You now have the position of 1st *, and the position of the 2nd *. How do you get the "FOR number of characters?

One minus the other: position of 2nd * - position of 1st * = number of characters from the position of the 1st * .

You have everything, just adapt them to match the syntax of SUBSTRING.

And instead of TRIM just add some +1 or -1 to the LOCATE, it's "faster" as you don't really call another function on top of everything but just start 1 char on the left or right to where you started in your formula.

Joe Choueiri-Oracle

Thank you Gianni, really really appreciate your help (not to mention you and Christian are always there to help)

Niharika Paramkusam-Oracle

Hi Team,

how can we fetch text between two characters in OBIEE the above example is relevant to me but it fetching extra characters. If you (Joe Choueiri-Oracle, Gianni Ceresa) got any resolution from mentioned above please guide me.

I am trying to fetch description of second segment from below :

AF Holding Limited.Facilities Management - Retail.Unspecified.Credit card receivables.Unspecified.Unspecified.Unspecified.Unspecified.Unspecified

Output Needed:

Facilities Management - Retail

Gianni Ceresa

https://community.oracle.com/tech/apps-infra/discussion/comment/16844645#Comment_16844645

If you do as described above, you get what you are after: you have a LOCATE function, you have basic arithmetic operators, you have the SUBSTRING function. With these you get what you want, it's all about simple logical formulas. Get the position of the first string separator you are after, get the position of the second, position of the second - position of the first is the length of the string, job done.

Niharika Paramkusam-Oracle

REQUIREMENT : NEED TO FETCH SECOND SEGMENT FROM

AF Holding Limited.Facilities Management - Retail.Unspecified.Credit card receivables.Unspecified.Unspecified.Unspecified.Unspecified.Unspecified

SOLUTION IN OBIEE/OTBI :

SUBSTRING("- PO Charge Account"."Code Combination Description" FROM LOCATE('.', "- PO Charge Account"."Code Combination Description")+1 FOR (LOCATE('.', "- PO Charge Account"."Code Combination Description", LOCATE('.', "- PO Charge Account"."Code Combination Description")+1)-LOCATE('.', "- PO Charge Account"."Code Combination Description"))-1)