xdo substr funtion with minus position — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

xdo substr funtion with minus position

Received Response
31
Views
2
Comments
User_U66J8
User_U66J8 Rank 4 - Community Specialist
in Oracle Analytics Cloud and Server

Summary

xdo substr funtion with minus position

Content

Hi All,

In XML Publisher 4.5.0 is it possible to use substr function with a starting position -4 ?

I'm trying the below:

<?xdofx:substr(‘PT507170490’, -4)?>

Returns the entire string: PT507170490

<?xdofx:substr(‘PT507170490’, 4)?>

Returns: 07170490

Any ideas why and if it's not supported, then do you know a possible workaround?

Regards,

Alex

Answers

  • Pavan kumar.
    Pavan kumar. Rank 5 - Community Champion

    Hi,

    As xdofx is the extended SQL function the above syntax was true.So as of SQL script from dual it's giving proper result below

    <span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> substr</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'<span style="color: #000000; font-family: Consolas, 'Courier New', Courier, mono, serif; font-size: 12px;">PT507170490</span>'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">-9</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> dual</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span>

    Output as:  507170490

    <?xdofx:substr('PT507170490',-9)?>

    Output should be similar to the above

    <span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span>

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    Hi ,

    In BI Publisher, substr function always start with 1, you cannot use -ve numbers (please refer the "Extended Function Support in RTF Templates" in BI publisher user guide)

    For your requirement you can calculate the length of string and then you can use substr to get the last 9 characters. Please use the below

    <?xdofx:substr('PT507170490',length('PT507170490')-8)?>

    Regards,

    Brajesh