Disclaimer

All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Tuesday, March 26, 2013

How to locate the reoccurring character in the string

Hello

Here is an interesting requirement. Look at the below report:




You notice that all of the PWE IDs have 3 '_'. Now I want to know the location of the last '_' in the string, what do I do?

OBIEE has locate and LocateN function at our disposal. However, the locateN function doesn't work that well, but the Locate Function does work.

So let's start with that:

Create a new column and enter: LOCATE('_', Pseudowire."PWE ID", -1)

the '-1' part is going to locate the '_' from the reverse side of the string, this is what we want


The resulting integers are the correct location of the last '_' if you count them :



Now, let's get rid of all of the strings after the last '_', maybe I will include the '_' as well.

Create another column in the report with the following formula:

Substring(Pseudowire."PWE ID" from 0 for LOCATE('_', Pseudowire."PWE ID", -1) -1)

Now we will use substring function. The 'for' part will be the location of the '_' that we located from the above step, and the 'from' part will be 0, which means we start from the beginning of the left side and we keep all of the strings up to the location of the last '_'. The last -1 in the formula is basically getting rid of the last '_' as well.




Now test:



Works perfectly.

Thanks

Until Next Time

2 comments:

Unknown said...

Great info. How would you pick up the string values before or after the special character?

i.e.

10083_HSPA_CD_10005

Result:

Column 1: 10083
Column 2: HSPA
Column 3: CD
Column 4: 10005

Appreciate your assistance.

RW said...

Hi,

I am looking at this post in 2020, but its awesome that way you explained it.

Can I ask how would you do it if we were to extract the 2nd substring (e.g. HSPA only from your 10083_HSPA_CD_10005)

I am unable to get a proper formula that will do that in OBIEE without causing performance issues.

Related Posts Plugin for WordPress, Blogger...