Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Alternative for INSTR() in OBIEE

Subhadeep GuhaAug 23 2016 — edited Aug 24 2016

I am currently working on a problem whereby I have to parse through a string and split the string into 2 sub strings. My string can have multiple underscore( _ ) and I want the last underscore( _ ) to be the separator between the 2 sub strings.

Example:

If the text is ABC, it should return 'ABC' & NULL.

If it's ABC_12, it should return 'ABC' & '12'

If the text is ABC_12_pqr_97, it should return 'ABC_12_pqr' & '97'.

I tried LOCATE function to find the position of the last underscore but it only gives you the position of the 1st occurrence of the underscore. What I need is the position of the Nth occurrence of that underscore(which basically INSTR() function in SQL does). Is there any alternate way of deriving it?
This post has been answered by Pedro F on Aug 23 2016
Jump to Answer

Comments

Pedro F
Answer

If you're looking to get the last underscore of a specific string than you can use the Locate function like this:

LOCATE('_','This_is_a_test_for_OTN' ,-1)

This will return you the numeric position of the last underscore in your string.

Marked as Answer by Subhadeep Guha · Sep 27 2020
Thomas Dodds

"What I need is the position of the Nth occurrence of that underscore(which basically INSTR() function in SQL does). Is there any alternate way of deriving it?" -- use the INSTR() in the PHYSICAL mapping on the logical column in the RPD... this way the function is shipped back to the database which can handle it better than OBIS/OBIPS.

Subhadeep Guha

Thanks Pedro! It worked. Appreciate your help!

Subhadeep Guha

Thanks Thomas for the response! It looks like a more performance friendly solution. I will definitely try it next time I make changes in the RPD. For this time, I needed a quick solution and I used LOCATE('','', -1) in the report.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 21 2016
Added on Aug 23 2016
#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee, #obiee
4 comments
2,394 views