INSTR gives incorrect results for negative index if characters repeat

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.

 

Comments

  • Sudhakar_BSudhakar_B Vertica Customer ✭✭

    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)

     

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file