SQLite Instr provides different value than expected

Question

I'm trying to pull a substring in a query using instr. The following command:

SELECT instr(ZSYNCPAYMENT, '{"t') 
FROM ZPAYMENT;

provides a result of 64.

I copied and pasted the output of SELECT ZSYNCPAYMENT FROM ZPAYMENT into a hex editor and selected the number of bytes up to and including the { symbol, which is part of my instr function above. The selected number of bytes shows as decimal length of 71. Why is my instr output showing a value of 64? The screenshot below is the output of the SELECT ZSYNCPAYMENT above.

Answer 1

From INSTR():

The instr(X,Y) function finds the first occurrence of string Y within string X and returns the number of prior characters plus 1, or 0 if Y is nowhere found within X. Or, if X and Y are both BLOBs, then instr(X,Y) returns one more than the number bytes prior to the first occurrence of Y, or 0 if Y does not occur anywhere within X.

In the hex editor you use you see the difference in bytes of the position of the string '{"t' from the start of the value of ZSYNCPAYMENT.
This is not the same as the difference in characters when a string contains unicode characters, which I suspect is the case with the string you posted in the image.

If you want the difference in bytes cast both ZSYNCPAYMENT and '{"t' to BLOBs:

SELECT INSTR(
         CAST(ZSYNCPAYMENT AS BLOB), 
         CAST('{"t' AS BLOB)
       ) 
FROM ZPAYMENT 

See a simplified demo.