Extract Zoned And Packed-Decimal Values From Character Fields, Take Two

Note: The code accompanying this article is available for download here.

Sometimes a reader of this august publication asks me about a problem that I don’t face, and I reply with an academic answer. But when I face the same problem, my interest ceases to be academic, and I look for a more practical solution. Such is the case with the use of SQL to extract packed- and zoned-decimal fields from a string of character data.

In the January 19, 2005, issue of Four Hundred Guru, I answered a question from Mary, who wanted to create a logical file that would redefine substrings as packed-decimal fields. I could only offer her an ugly SQL solution.

The question remained academic until recently, when I was asked to work on an application that includes a program-described file (a “flat” file) that contained three packed-decimal fields. Suddenly the technique I gave Mary was too much trouble, so I came up with something a little more practical.

I wrote two SQL functions to convert zoned- and packed-decimal to a format that the DECIMAL function can work with. Since they are like the CHAR function in that they convert data to a human-readable format, I called them ZonedChar and PackedChar. I really don’t like those names. I thought Unpack would be a good name for the packed-decimal routine, but then I would have had to call the zoned-decimal function Unzone, and that didn’t make sense to me. If you have better names than ZonedChar and PackedChar, please let me know. I’m always looking for, and am receptive to, a better idea.

Both functions return a string of digits that may begin with a hyphen and may include a decimal point. If the zone portion of the low-order byte of a zoned-decimal number or the digit portion of the low-order byte of a packed-decimal number is X’B’ or X’D’, the number is considered to be negative. Values X’A’, X’C’, X’E’, and X’F’ are interpreted as positive. IBM‘s practice is to use X’F’ for positive numbers and X’D’ for negative numbers. (To learn more about the structure of zoned-decimal and packed-decimal formats, follow these links: Zoned-Decimal Format and Packed-Decimal Format.)

Here are some examples of result strings.

select substr(decdata,1,3) as Char,
       dec( ZonedChar( substr(decdata,4,5) ,0),5,0) as Zoned1,
       dec( ZonedChar( substr(decdata,9,5) ,2),5,2) as Zoned2,
       dec( PackedChar(substr(decdata,14,4),0),7,0) as Packed1,
       dec( PackedChar(substr(decdata,18,3),2),5,2) as Packed2,
       dec( PackedChar(substr(decdata,21,4),2),7,2) as Packed3
from decdata

CHAR  ZONED1   ZONED2   PACKED1   PACKED2   PACKED3
====  ======   ======   =======   =======   =======
AAA        0      .00         0       .00       .00
BBB       10    10.00        10     10.00     10.00
CCC       10-   20.00-       30-    40.00-    50.00-
DDD        1     2.34         5      6.78      9.01