I am sure that the person who asked me if this was possible is not the first person to have tried, and was unable to get it to “work”.
They had been presented with a “flat” file where the first two characters denoted the length of the key value. What they need to do was to extract the variable length key into another value they could use.
The file was like this:
DATA
———————————-
10<– 10 –>XXXXXXXXXXXXXXXXXXX…
05<-5->XXXXXXXXXXXXXXXXXXXXXXXX…
20<——- 20 ——->XXXXXXXXX…
It is easy to extract the first two characters from DATA using the following SQL Select statement:
01 SELECT SUBSTR(DATA,1,2) AS “Length”,
02 DATA
03 FROM TESTFILE ;
Which returns:
