Doubt while sql

Question

I need to get all five figures before the ~3L of the XXX:

~1BLB~2B1C~3L~1TPL~2B39~3L~1RAD~2C1D~3L  

And so on and so forth.

I already managed to make it takes the first value before the 3L with this select:

db2 "select SUBSTR(FH01XXX.pnrstring,(POSSTR(FH01XXX.pnrstring,'~3L')-5),5) AS PRNR from UFHDBXXX.fh01tXXX'"

I just can’t think of a way for him to "sweep" the string again to catch the others before the 3L.

---

Thank you so much for the answers, but they still haven’t solved my problem for 2 reasons:

1. I get this string from another system, its content is variable, but there will always be this "~3L".

2. I need to think in a while, for they are about 20 "~3L", and I need to show the 5 characters before each "~3L".

Answer 1

Good morning.

You could try.

Catch before the ~3L:

SELECT substring('~1BLB~2B1C~3L~1TPL~2B39~3L~1RAD~2C1D~3L' from 1 for 10);

Catch after the ~3L:

SELECT substring('~1BLB~2B1C~3L~1TPL~2B39~3L~1RAD~2C1D~3L' from 15);

Using substring to show a value up to such a value.

The code used is for bd Postgresql but contains the same concept for other databases, including SQL.

Hugs.

Answer 2

You can also ultilize INSTR to capture the first occurrence of "~3L"

SELECT substring('~1BLB~2B1C~3L~1TPL~2B39~3L~1RAD~2C1D~3L'  from 1 for INSTR('~1BLB~2B1C~3L~1TPL~2B39~3L~1RAD~2C1D~3L', '~3L'));