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

• Joel Coehoorn: No one ever says "a code" when referring to program source code. It's "a snippet of code", or "an excerpt of code", or "a sample of code", or just "code".
• Error_2646: What DBMS are you using? And, first things first, what are you actually trying to do? Get the characters between the 2nd and 3rd occurrence of '*'?
• Error_2646: @Joe I can reproduce that error in Teradata when the string does not have a 3rd occurrence of '*'. Are you sure all records have at least 3 asterisks?
• Error_2646: @Joe Teradata has a function for this though. As long as you aren't on a very old version. strtok('XBR*jkhehj8f22*3012367134*CVHI*N* X*G*P', '*',3)
• Error_2646: @Joe STRTOK() will handle that for you. You should use STRTOK if you can. A hack would be to concatenate 3 *'s to the end of the string then do this transformation. That'll return empty string if it reaches your padding. Otherwise you'll need a case expression with a regex replace, i.e. remove all non-*'s characters and check the length. If it's less than 3, then null. Else do the transformation.

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.

• Joe: In the same spot, but Teradata kept giving me and out of bounds error
• Joe: I wish I could change the schema but this is for a company in which I have no say in that whatsoever
• Salman Arshad: Check if it works for the example string (hardcode that string instead of expression) then try with a substring that has less than 3 stars. If it fails, you need to use case expression to handle those cases.