Doubt while sql

Question

Navigation

#1 by (2 votes)
#2 by (1 votes)

4

I need to get all 5 values before ~3L of XXX:

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

And so on.

I've been able to get it to get the first value before 3L with this select:

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

I just can not think of a way to "scan" the string again to get the others before 3L .

Many thanks for the answers, but they still have not solved my problem for 2 reasons:

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

I need to think of a while, since they are approximately 20 "~3L" , and I need to display the 5 characters before each "~3L" .

sql db2

asked by anonymous 05.01.2015 / 14:44

2 answers

1

You can also use 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'));

05.01.2015 / 16:02

Get a value inside the HTML cURL Include HTML in HTML without affecting SEO

score 2 · Accepted Answer

Good morning.

You could try.

Pick up before ~ 3L:

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

Take after ~ 3L:

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

Using substring to show a value up to that value.

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

Hugs.