Pages

Wednesday, May 12, 2021

Using a result of a substring for the length value of another substring

nested substring in sql

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:

Length  DATA
------  ----------------------------------
10      10<-- 10 -->XXXXXXXXXXXXXXXXXXX...
05      05<-5->XXXXXXXXXXXXXXXXXXXXXXXX...
20      20<------- 20 ------->XXXXXXXXX...

Let me try to use the code I used to generate the "Length" to substring the key value from DATA:

01  SELECT SUBSTR(DATA,1,2) AS "Length",
02         SUBSTR(DATA,3,SUBSTR(DATA,1,2)) AS "What I want",
03         DATA
04    FROM TESTFILE ;

Line 2: You can see the nested substring as the third part of the first substring.

Alas, this statement errors with the following message. As I am using ACS's "Run SQL Scripts" the message appears in the results/messages window.

SQL State: 42815 
Vendor Code: -171 
Message: [SQL0171] Argument 3 of function SUBSTR not valid. 
Cause . . . . . :   The data type, length, or value of argument 3
of function SUBSTR specified is not valid.

This is not a surprise as substring returns a character result. Let me convert the returned character value to a number by using the TO_NUMBER built in function:

01  SELECT TO_NUMBER(SUBSTR(DATA,1,2)) AS "Length",
02         DATA
03    FROM TESTFILE ;

The results look good. In the second result the 5 has lost its leading zero as it is now a number, and not a character value.

Length  DATA
------  ----------------------------------
10      10<-- 10 -->XXXXXXXXXXXXXXXXXXX...
5       05<-5->XXXXXXXXXXXXXXXXXXXXXXXX...
20      20<------- 20 ------->XXXXXXXXX...

Let me try using the TO_NUMBER in the nested substring:

01  SELECT TO_NUMBER(SUBSTR(DATA,1,2)) AS "Length",
02         SUBSTR(DATA,3,TO_NUMBER(SUBSTR(DATA,1,2))) AS "What I want",
03         DATA
04    FROM TESTFILE ;

This errors with the same error as before, SQL state 42815.

Every time I use TO_NUMBER I always receive some comments asking me why I did not use a CAST to convert the character to decimal. Let me try to cast instead:

01  SELECT CAST(SUBSTR(DATA,1,2) AS DEC(2,0)) AS "Length",
02         SUBSTR(DATA,3,CAST(SUBSTR(DATA,1,2) AS DEC(2,0))) AS "What I want",
03         DATA
04    FROM TESTFILE ;

Using the CAST made no difference and the statement errors with the same SQL state as before.

To be able to use a nested substring it must return an integer value. In the example below I have defined the result as integer, INT. I have not bothered to give the integer value a length as Db2 for i will define it with a default length.

My statement looks like:

01  SELECT CAST(SUBSTR(DATA,1,2) AS INT) AS "Length",
02         SUBSTR(DATA,3,CAST(SUBSTR(DATA,1,2) AS INT)) AS "What I want",
03         DATA
04    FROM TESTFILE ;

And when I execute the above statement I get the results I desired:

Length  What I want           DATA
------  --------------------  ----------------------------------
10      <-- 10 -->            10<-- 10 -->XXXXXXXXXXXXXXXXXXX...
05      <-5->                 05<-5->XXXXXXXXXXXXXXXXXXXXXXXX...
20      <------- 20 ------->  20<------- 20 ------->XXXXXXXXX...

I have the key values wanted extracted from DATA.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

4 comments:

  1. I never use CAST. I just use the data type. Is there really a difference?
    SELECT INT(SUBSTR(DATA,1,2)) AS "Length",
    SUBSTR(DATA,3,INT(SUBSTR(DATA,1,2))) AS "What I want", DATA
    FROM TESTFILE

    ReplyDelete
  2. I know this sounds strange, but could u try inserting a space after the commas in the SUBSTR?

    ReplyDelete
    Replies
    1. Do you mean rather than have SUBSTR(DATA,1,2) have SUBSTR(DATA, 1, 2)?
      Of course you can. Just do what feels most comfortable to you.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.