Pages

Wednesday, January 3, 2018

Determining length of a string using SQL

calculating length of data within a column

This is just a quickie in response to a question I was asked: How can I determine the length of string within a column using SQL?

This is very simple as there is Db2 for i scalar function to do this LENGTH. But before I do that let me explain why using this scalar function is simpler than doing the same using RPG.

I have a simple DDL table of just one column. There is no "NOT NULL", therefore, this column can be null.

CREATE OR REPLACE TABLE QTEMP.TESTFILE (
  FIELD1 VARCHAR(80)
) ;

And it contains the following data:

....+....1....+....2....+....3....+....4....+....5...
FIELD1
This is the first record
The second record is much, much longer than the first
-

The third row is null.

If I was doing this in RPG I would have to…

  1. Read the file
  2. Is the file's field null?
  3. Calculate the length of the string in the field
01  dow (1= 1) ;
02    read INPUT ;
03    if (%eof) ;
04      leave ;
05    endif ;

06    if %nullind(FIELD1) ;
07      Length = 0 ;
08    else ;
09      Length = %len(FIELD1) ;
10    endif ;
11  enddo ;

Using SQL I can determine if the column is null, and if not calculate the length of the string in the definition of the cursor. Below is my example program. I am going to use a multiple row fetch to get all the data from the table in one Fetch statement.

01  **free
02  ctl-opt option(*nodebugio:*srcstmt) ;

03  exec sql SET OPTION CLOSQLCSR = *ENDMOD ;

04  dcl-ds Data qualified dim(99) ;
05    Length int(5) ;
06  end-ds ;

07  dcl-s Rows int(5) inz(%elem(Data)) ;

08  exec sql DECLARE C0 CURSOR FOR
09             SELECT IFNULL(LENGTH(FIELD1),0)
10              FROM QTEMP.TESTFILE
11            FOR READ ONLY ;

12  exec sql OPEN C0 ;

13  exec sql FETCH C0 FOR :Rows ROWS INTO :Data ;

14  exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;

15  exec sql CLOSE C0 ;
16  *inlr = *on ;

Line 1: need this so the compiler knows this program is in totally free RPG.

Line 2: My favorite control options.

Line 3: I always put these Set Option statements in my source code to ensure that someone else will not forget the preferred create options.

Lines 4 - 6: This is the data structure I will be fetching the data into.

Line 7: I am using this variable for two purposes. Before the Fetch it contains the number of elements in the data structure array. After the Fetch the number of rows retrieved from the table.

Line 8 – 11: Is my cursor definition.

Line 9: If FIELD1 is null then the IFNULL will translate null to zero. Which is what I want as a null value for FIELD1 means that there is no string in it.

Line 11: I always like to add this so that others will know that the table is only being used for input.

Line 12: I need to open the cursor before I can use it.

Line 13: The multiple row fetch will retrieve the same number of rows as there are elements in the data structure array into the data structure array.

Line 14: I am using the Get diagnostic to get the number of rows fetched.

Line 15: As I am finished with the cursor I need to close it.

So what does my data look like when I run this program?

The data structure array looks like:

> EVAL data
  DATA.LENGTH(1) = 24
  DATA.LENGTH(2) = 53
  DATA.LENGTH(3) = 0
  DATA.LENGTH(4) = 16448
  DATA.LENGTH(5) = 16448

the three rows from the table were successfully loaded into the first three elements of the array. The '16448' means that this element is unused.

The value in the Rows variable is the number of rows fetched. If I was going to "read" the elements in the array I would use it to make sure I would not read more than the third element.

> EVAL rows
  ROWS = 3

Yes, it is really that simple to calculate the length of the string in SQL.

 

You can learn more about the LENGTH SQL scalar function from the IBM website here.

 

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

6 comments:

  1. Simon, in RPG one can determine the length of the value in a VarChar with %Len(%TrimR(field_X)) and in SQL/PL with LENGTH(RTRIM(field_X)). Unless I am missing something, your aproach seems overly complicated? And without first trimming the VarChar in question you won't be able to get get actual length of the value.

    ReplyDelete
    Replies
    1. All of the code examples I have given were tested, and the what is shown are the results generated by that code.

      Delete
    2. Granted, however without the %TrimR() or RTRIM() before determining the value length, the VarChar field could have trailing blanks, making the logic unreliable because it does not trim.

      Delete
    3. My results show that the LENGTH in the SQL statement is disregarding the training blanks.

      Delete
  2. Another great skill with the new tools! very valuable for us who need to get the solutions!

    ReplyDelete
  3. Simon and Dan... regarding to trim or not to trim, it all depended upon the requirements... Since the column is defined as VarChar(80) there will be a hidden 2 byte small integer stored internally in the column that contains the length of the data present. In simon program whatever value was stored in the VarChar column will update that small int prefix. If the value was trimmed before its written then the prefix will store that. If unlike Char(80) column trail blanks are not automatically stored in the data.

    ReplyDelete

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.