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