SQL functions that check the validity of character and numeric data in your RPG code can be implemented by using the hexadecimal representation of the data and the SQL predicate REGEXP_LIKE.

Considering the hexadecimal representation of data, it doesn't seem to be difficult to find regular expressions that allow us to check whether character and numeric fields contain valid values.

So, to check whether a value is valid, we can define an SQL function that obtains the hexadecimal representation of its parameter (SQL function HEX) and checks it with the corresponding regular expression. However, as calling our function with an invalid numeric argument would raise an "invalid data" error, we will take the function HEX out of our SQL function to avoid that error.

Function ISDEC

The function ISDEC can be implemented as follows:

CREATE FUNCTION QGPL/ISDEC(HEXPARM VARCHAR(64))

   RETURNS CHAR(1)

   LANGUAGE SQL

   CALLED ON NULL INPUT

CASE

WHEN HEXPARM IS NULL THEN

   RETURN '0';

WHEN REGEXP_LIKE(HEXPARM, '^([0-9][0-9])*[0-9][CDF]$') THEN

   RETURN '1';

ELSE

   RETURN '0';

END CASE;

The argument used to call this function must contain the hexadecimal representation of the field or variable that we want to check, not the field or variable itself. For example, we could invoke this function as follows:

SELECT * FROM FILE1

WHERE ISDEC(hex(FIELD1)) = '0'

The length of the parameter is 64 as a decimal value can have up to 63 digits (occupying 32 bytes), and the hexadecimal representation of a field takes up twice its size in bytes.

If the function ISDEC is called with an argument of type CHAR, the SQL function resolution will promote this type to VARCHAR—provided that there is no other function ISDEC with a CHAR parameter—and, as a consequence, the function we have just defined will be selected.

The regular expression used in the predicate REGEXP_LIKE checks whether the parameter is made up, from beginning (^) to end ($), of zero or more pairs of hexadecimal digits from 00 to 99. The final pair must consist of a digit from 0 to 9, and one of the letters C, D, or F.

The definition of this and the following functions can be downloaded from here.

Function ISNUM

This function can be defined as follows:

CREATE FUNCTION QGPL/ISNUM(HEXPARM VARCHAR(126))

...

WHEN REGEXP_LIKE(HEXPARM, '^(F[0-9])*[DF][0-9]$') THEN

   RETURN '1';

...

The regular expression used in this case allows pairs of hexadecimal digits from F0 to F9, except the last one, which can also be between D0 and D9.

Function ISCHAR

This function can be implemented as follows:

CREATE FUNCTION QGPL/ISCHAR(HEXPARM VARCHAR(32739))

...

WHEN REGEXP_LIKE(HEXPARM, '^([4-E].|F[0-E])*$') THEN

   RETURN '1';

...

The regular expression used checks whether the parameter is made up of zero or more pairs of hexadecimal digits from 40 to EF or from F0 to FE—that is, whether the parameter contains only displayable characters.

In this case, we could use the function HEX inside our function, as shown next:

CREATE FUNCTION QGPL/ISCHAR2(PARM VARCHAR(16336))

...

CASE

WHEN PARM IS NULL THEN

   RETURN '0';

WHEN REGEXP_LIKE(hex(PARM), '^([4-E].|F[0-E])*$') THEN

   RETURN '1';

ELSE

   RETURN '0';

END CASE;

Tests

The following CL and SQL statements can be used to test the defined functions:

CRTPF FILE(QTEMP/FILE1) RCDLEN(21)

Source member FILE2:

               R RFILE2

                 F1             1S 0

                FCHAR          5A         ALWNULL

                 FVCHAR         5A         ALWNULL VARLEN

                 FDEC           5P 2       ALWNULL

                 FNUM           5S 2       ALWNULL

CRTPF FILE(QTEMP/FILE2) SRCFILE(QDDSSRC)

insert into QTEMP/FILE1 values

('1' || X'C100404040' || X'0001' || 'B   ' ||

   X'00101F' || X'F0F0F2F0F2'),

('2' || 'A   '       || X'0002' || X'C200404040' ||

   X'00101F' || X'F0F0F2F0F2'),

('3' || 'A   '       || X'0001' || 'B   ' ||

   X'0010EF' || X'F0F0F2F0F2'),

('4' || 'A   '       || X'0001' || 'B   ' ||

   X'00101F' || X'F0F0F2F0FE'),

('5' || 'A   '       || X'0001' || 'B   ' ||

   X'00101F' || X'F0F0F2F0F2')

CPYF QTEMP/FILE1 QTEMP/FILE2 MBROPT(*REPLACE) FMTOPT(*NOCHK)

insert into QTEMP/FILE2 values

(6, null, 'B', null, 2.02),

(7, 'A', null, 1.01, null)

select F1, hex(FCHAR), hex(FVCHAR), hex(FDEC), hex(FNUM)

from QTEMP/FILE2

where ischar(hex(FCHAR)) = '0'

or

where ischar(hex(FVCHAR)) = '1'

or

where ischar2(FCHAR) = '0'

or

where isdec(hex(FDEC)) = '0'

or

where isnum(hex(FNUM)) = '0'

or

select substr(FILE1, 1, 1) as F1,

       hex(substr(FILE1, 14, 3)) as FDECH

from QTEMP/FILE1

where isdec(hex(substr(FILE1, 14, 3))) = '0'

Juan Macias has a Degree in Mathematics (1988) and a Master in Data Management from the Universidad Complutense de Madrid (1989). He's a developer of RPG and COBOL applications mainly for finance and insurance companies. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL