Dynamic starting position in substring and dynamic length

Question

I have code that for the most part is working as intended. However, it is returning one character too many at the end. I tried adding -1 and I get an out of bounds error. What am I doing wrong?

SELECT
  SUBSTR(
    t.variable, 
    INSTR(t.variable, '*', 1, 2) + 1, 
    INSTR(t.variable, '*', 1, 3) - INSTR(t.variable, '*', 1, 2)
  ) 
FROM test t;

I tried -1, +1. The -1 gives me an outbound error and the +1 adds on an extra character.

Sample value:

XBR*jkhehj8f22*3012367134*CVHI*N* X*G*P

Answer 1

Where did you put the -1? Put it in the right place and this seems to work:

SELECT
  SUBSTR(
    t.variable, 
    INSTR(t.variable, '*', 1, 2) + 1, 
    INSTR(t.variable, '*', 1, 3) - INSTR(t.variable, '*', 1, 2) -1
  ) 
FROM test t;

See it here:

https://dbfiddle.uk/mXkc0JXb

But mostly, my suggestion is to fix the schema, because delimited data within a column like this usually really is a broken design. This cries out for an additional table with (likely) 9 columns: one for the primary key from this table (and a unique constraint), and one for each of the 8 *-delimited fields from this column.