Reporting and Analytics for ERP

Get Involved. Join the Conversation.


    Murali Gangidi
    SUBSTRING based on non-numeric character in the expression...
    Topic posted December 31, 2018 by Murali GangidiBronze Medal: 1,250+ Points, tagged BI Publisher, OTBI, Reports 
    141 Views, 4 Comments
    SUBSTRING based on non-numeric character in the expression (OTBI Analysis)
    SUBSTRING based on non-numeric character in the expression for OTBI Report

    Hi All,

          I have a requirement in OTBI Report, to get the 20 characters after the string 'XYZ' from an expression. If the expression doesn't have the string 'XYZ' then the first 15 characters. The expression is not fixed length.

    If the expression is fixed length, i can use SUBSTR(<expression>,1,20) if the expression has 'XYZ' if not SUBSTR(<expression>,1,15).

    So, instead of 1 i have to use XYZ because this string 'XYZ' may be anywhere in the expression.Could anyone help me on this scenario




    • M Kashif Raza

      Hi Murali, 

      Do you need to write such condition in Pl/sql query ?

      if Yes, then you may use Decode() and instr() method in PLSQL to achieve your desired goal.

      DECODE( instr(<expression>,'XYZ') ,0, SUBSTR(<expression>,1,15) , SUBSTR(<expression>,1,20))

      "The INSTR function returns a numeric value. The first position in the string is 1.
      If substring is not found in string, then the INSTR function will return 0."

      M Kashif


    • Alexey Shtrakhov

      Hi Murali,

      you can use following formula for your field:

      SUBSTRING(expr FROM POSITION('XYZ' IN expr)+3 FOR 20)

      where exp is your source field and XYZ is the value you try to find.