Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Alternative for INSTR() in OBIEE
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?
Answers
-
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.
0 -
"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.
0 -
Thanks Pedro! It worked. Appreciate your help!
0 -
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.
0