Skip to main content

INSTR gives incorrect results for negative index if characters repeat

  • October 18, 2016
  • 1 reply
  • 5 views
KKirkpatrick
Forum|alt.badge.img+1

Version 7.2

 

SELECT INSTR('1+2+3','+',-1,2);

Result (correct): 2

 

SELECT INSTR('1++3','+',-1,2);

Result (incorrect): 0

 

FWIW, INSTRB is not affected by this bug and serves as a viable workaround for (rare) cases that this arises.

 

    1 reply

    Sudhakar_B
    Forum|alt.badge.img+1
    • Sudhakar_B
    • Participating Frequently
    • October 26, 2016

    Kirk,

    This exists in 8.0 as well :(

    dbadmin=> SELECT INSTR('1+2+3','+',-1,2);
     INSTR
    -------
         2
    (1 row)

    dbadmin=>
    dbadmin=> SELECT INSTR('1++3','+',-1,2);
     INSTR
    -------
         0
    (1 row)

    dbadmin=> select version();
                  version               
    ------------------------------------
     Vertica Analytic Database v8.0.0-0
    (1 row)

    dbadmin=> SELECT INSTR('1++34567','+',-1,2);
     INSTR
    -------
         0
    (1 row)

     

     

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement