Pre-Defined Functions of Upper, SubString and INSTR

Hugo Mendoza shared this question 3 weeks ago
Answered

Hi there -

In the Report builder, I see Pre-Defined functions (from the Calculated field editor) that there are options for Upper and SubString. Is there a Pre-defined function for INSTR?

Hugo

Comments (3)

photo
1

Hi Hugo,

no but there can be! Those Pre-defined functions are defined in a file called custom-functions.xml in the path <yellowfin>/appserver/webapps/ROOT/WEB-INF/ and if you follow the instructions in the following wiki article then you can make your own:

https://wiki.yellowfinbi.com/display/USER74/Calculated+Fields#CalculatedFields-CreatingNewCustomFunctions

However, because you are a much valued customer of Yellowfin I have gone ahead and assumed that you are using an Oracle database and have created the code for you, so all you have to do is to insert the below code just before the last line

"</custom-functions>"

of the afore-mentioned custom-functions.xml file:


  <!-- Oracle INSTR() -->
  <function>
    <name>InStr</name>
    <argument>
      <index>1</index>
      <name>column name</name>
      <datatype>text</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>substring(to search for)</name>
      <datatype>text</datatype>
    </argument>	
    <argument>
      <index>3</index>
      <name>from(which char to start search from)</name>
      <datatype>numeric</datatype>
    </argument>	
    <name>InStr</name>
    <argument>
      <index>4</index>
      <name>occurence (1st, 2nd etc)</name>
      <datatype>text</datatype>
    </argument>	
    <sql>
      <![CDATA[
        INSTR($1, $2, $3, $4)
      ]]>
    </sql>
    <database>Oracle</database>	
    <return>numeric</return>
  </function>  
I hope you enjoy it!

regards,

David

photo
1

Hi David --

Thank you very much -- I appreciate this!

Hugo

photo
2

You're welcome Hugo!

I hope that means my guess about you using Oracle was correct!

regards,

David