Convert number to hexadecimal with SQL

In an earlier post I wrote about how to convert a character string to hexadecimal, and back to character again. The obvious question followed:

What if wanted to convert a number to hexadecimal with SQL?

One of the issues is that numbers come in different forms in modern RPG and in SQL. Here are the common types of numbers I encounter:

Type	RPG	SQL
Packed	PACKED	DECIMAL, DEC
Signed	ZONED	NUMERIC, NUM
Integer	INT	INTEGER, INT, SMALLINT, BIGINT

In this post I am going to give examples of how to convert each of these RPG number types to hexadecimal, and then back again.

This is my program:

01 **free 02 dcl-s Packed packed(7 : 2) inz(12345.67) ; 03 dcl-s Result1 like(Packed) ; 04 dcl-s Zoned zoned(7 : 2) inz(12332.1) ; 05 dcl-s Result2 like(Zoned) ; 06 dcl-s Integer int(10) inz(-153) ; 07 dcl-s Result3 like(Integer) ; 08 dcl-s Hex varchar(20) ; 09 exec sql SET :Hex = HEX(:Packed) ; 10 dsply Hex ; 11 exec sql SET :Result1 = INTERPRET(HEXTORAW(:Hex) AS DEC(7,2)) ; 12 dsply %char(Result1) ; 13 exec sql SET :Hex = HEX(:Zoned) ; 14 dsply Hex ; 15 exec sql SET :Result2 = INTERPRET(HEXTORAW(:Hex) AS NUM(7,2)) ; 16 dsply %char(Result2) ; 17 exec sql SET :Hex = HEX(:Integer) ; 18 dsply Hex ; 19 exec sql SET :Result3 = INTERPRET(VARBINARY_FORMAT(:Hex) AS INT) ; 20 dsply %char(Result3) ; 21 *inlr = *on ;

Lines 2 – 7: These are the definitions for the variables I am using in this program.

Lines 2 and 3: Here is the packed variable, and its equivalent packed result variable.

Lines 4 and 5: Zoned variable and its equivalent zoned result variable.

Lines 6 and 7: Integer variable and the integer result variable.

Line 8: The "hexadecimal" variable defined as a variable length character of 20 bytes.

Line 9: Using SQL's Set statement with the HEX scalar function I convert the value in the Packed variable into a hexadecimal value in the Hex variable.

Line 10: I use the Display operation code to show the value in the variable Hex.

Line 11: Using SQL's Set statement to convert the hexadecimal value into a packed number, which will be in the RPG variable Result1. To do this I first need to convert the value in Hex to a binary string. I use the HEXTORAW scalar function to do that. The HEXTORAW is enclosed within an INTERPRET scalar function. The Interpret will interpret the character string passed to it as the data type given, in this case a Decimal value of seven long, with two decimal places.

Line 12: The value in Result1 is displayed.

When the program is called the following would be displayed:

DSPLY 1234567F DSPLY 12345.67

Lines 13 – 16: Next number type to convert is a zoned numeric.

Line 13: The value in the RPG variable Zoned is converted to hexadecimal and the result is placed in the RPG variable Hex.

Line 15: The HEXTORAW and INTERPRET are used again to convert the value in Hex into a zoned number. The difference between this line and the one for the packed value, line 11, is that the data type for the Interpret is a numeric SQL data type, 7 long with 2 decimal places.

The values displayed for this section of the program are:

DSPLY F1F2F3F3F2F1F0 DSPLY 12332.10

Lines 17 – 20: The final number type is the integer.

Line 17: Is basically the same as the equivalents of the other number types, as the value in the RPG variable Integer is converted to a hexadecimal string by the HEX scalar function.

Line 19: This line uses a different scalar function, VARBINARY_FORMAT, to convert the value in Hex to a binary string. IBM's documentation explains that VARBINARY_FORMAT and HEXTORAW are very similar. I could not get the valid value return for the hexadecimal equivalent of the integer if I used HEXTORAW, but I could with VARBINARY_FORMAT. This is why I use it here.

The values shown for this part are:

DSPLY FFFFFF67 DSPLY -153

I believe the program answers the question that was asked in a simple and straight forward way.

 

You can learn more about this from the IBM website:

• HEXTORAW SQL scalar function
• VARBINARY_FORMAT SQL scalar function

 

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