Determining length of a string using SQL

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.