4 Replies Latest reply on Aug 24, 2016 9:54 PM by Subhadeep Guha

    Alternative for INSTR() in OBIEE

    Subhadeep Guha

      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.


      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?