Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Alternative for INSTR() in OBIEE

Received Response
761
Views
4
Comments
Subhadeep Guha
Subhadeep Guha Rank 1 - Community Starter
edited Aug 13, 2024 6:13PM in Oracle Analytics Forum

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

  • Pedro F
    Pedro F Rank 6 - Analytics & AI Lead

    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.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics & AI Strategist

    "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
    Subhadeep Guha Rank 1 - Community Starter

    Thanks Pedro! It worked. Appreciate your help!

  • Subhadeep Guha
    Subhadeep Guha Rank 1 - Community Starter

    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.