Newsletters   Subscriptions  Forums  Store   Career  Media Kit  About Us  Contact  Search   Home 
Volume 5, Number 3 -- January 19, 2005

Extracting Zoned and Packed Decimal Values from Character Fields

Hey, Ted:

I have a physical file that includes a large alphanumeric field that is defined differently for many different record types. I am creating logical files for each of the record types and must redefine this large field in different ways for different record types. For some of the record types the field contains packed decimal values. How do I define a substringed field (one created with the SST keyword in DDS) as packed decimal?


Your situation is not unusual, especially in shops that use packaged software. Since developers can't foresee everything users may have to store, they sometimes include extra fields, sometimes called "user fields," in which users can store anything they like. Querying these fields can be a challenge when numeric values are embedded in these fields.

Substringed fields are always character, so the logical file route is not going to work for you. I have an SQL alternative that you may want to consider. It's as ugly as a mud fence, but it works.

Let's assume a large character field, USRF01, that contains three numeric values. In RPG programs, you would define this field as a data structure in order to subdivide it.

D usrf01          ds
D   TypeCode              1      2s 0
D   Category              3      4p 0
D   Amount                5      8p 2

You'll need to use SQL's substring function to extract these fields.


You might make some sense of the first field, which is in zoned decimal format, but the packed fields won't be useful. You'll need to do more in order to convert these fields into valid numeric values. What you have to do depends on two things--whether a field is in packed or zoned format, and whether a field can have a negative value or not.

Let's look first at the zoned decimal field, TypeCode. Usually codes, account numbers, and anything else that is not a quantity or currency amount do not have negative values, so there is no need to allow for negatives in this case. Use the decimal function or integer function to convert the substring to a numeric value.

select decimal(substr(usrf01,1,2),2,0) as TypeCode
  from usrfldpf

select dec(substr(usrf01,1,2),2,0) as TypeCode
  from usrfldpf

select integer(substr(usrf01,1,2)) as TypeCode
  from usrfldpf

select int(substr(usrf01,1,2)) as TypeCode
  from usrfldpf

This solution is easy, but it wouldn't work if TypeCode could be negative. Let me come back to negative zoned values after I show how to handle negative packed values.

Let's look at the category subfield. Like TypeCode, it will not contain negative values, or at least it shouldn't, so extracting its value is not so difficult. Convert the substring to its hexadecimal representation and extract all but the last hex digit, which indicates the sign. For example, a category code of 123 is hexadecimal 123F. Once you've extracted all but the last hex digit, use either decimal or integer to convert to numeric.

select dec(substr(hex(substr(usrf01,3,2)),1,3),3,0)
           as Category
  from usrfldpf

Here the inner substring function extracts two bytes beginning with the third byte. The hex function converts it to four characters that indicate the hex digits. The outer substring function extracts the first three characters, which the decimal function converts to numeric format.

Now take a look at the Amount subfield. There are two complications to consider--the field can be negative, and it contains a decimal point.

As with the category, you'll need to work with the hex digits of this field in order to convert to numeric. The last hex digit is the sign, so ignore it for now. The following extracts the digits of the number, inserting a decimal point in the proper position.

select dec(
       7,2) as Price
  from usrfldpf

To make the number positive or negative, check the last hex digit. If the digit is D, the number is negative. Otherwise, assume it's positive. (This is not a valid assumption on all systems, but it works fine under DB2/400.) If you find a D, multiply the number you extracted by negative one. Otherwise, multiply by 1.

select dec(
         7,2) *
         (case when substr(hex(substr(usrf01,5,4)),8,1) = 'D'
                 then -1 else 1 end),
       7,2) as Price
  from usrfldpf

The only loose end to tie up is what to do about zoned fields that can be negative. We'll return to the TypeCode field for an example, even though such a field is unlikely to have a negative value.

As with packed fields, you must convert the zoned field to hexadecimal and extract all the digits. You will need to extract the second digit of each pair, since the hex value of a number is either a D or F followed by the digit itself. That is, the value 250 is represented in hex as F2F5F0. The first hex digit of the last pair contains the sign of F or D.

Since TypeCode is two digits, the following calculation extracts its value. The second digit of each hex pair is concatenated to form a string, which is converted to numeric with the decimal function. This converted value is multiplied by positive 1 or negative 1, depending on the first hex digit of the last pair, in order to apply the sign to the number.

select dec(
        dec(substr(hex(usrf01),2,1)||substr(hex(usrf01),4,1),2,0) *
        (case when substr(hex(substr(usrf01,1,2)),3,1) = 'D'
              then -1 else 1 end)
       ,2,0) as TypeCode
  from usrfldpf

I told you it wouldn't be pretty. In a perfect world, we wouldn't have to resort to such gymnastics. If my more than 23 years in this industry has taught me anything, it's that we don't live in a perfect world.

I have overlooked the obvious before. I hope I am doing so again. Maybe someone out there can show all of us a better solution to an all-too-common problem.


Click here to contact Ted Holt by e-mail.

Sponsored By

Do you need area code information?
Do you need ZIP Code information?
Do you need ZIP+4 information?
Do you need city name information?
Do you need county information?
Do you need a nearest dealer locator system?

We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

Just call us and we'll arrange for 30 days FREE use of either

WorksRight Software, Inc.
Phone: 601-856-8337
Fax: 601-856-9432
Web site:

Technical Editors: Howard Arner, Joe Hertvik, Ted Holt,
Shannon O'Donnell, Kevin Vandever
Managing Editor: Shannon Pastore
Contributing Technical Editors: Joel Cochran, Wayne O. Evans, Raymond Everhart,
Bruce Guetzkow, Marc Logemann, David Morris
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.


Advanced Systems Concepts
WorksRight Software
Guild Companies


Date-Handling in CL Procedures

Extracting Zoned and Packed Decimal Values from Character Fields

Admin Alert: More on Preparing for OS/400 V5R1 to V5R3 Upgrades

The Four Hundred
IBM to Promote the iSeries During the NFL Playoffs

Migration to Java Is Paying Off, Intentia Says

Will IT Vendors Set Up a Patent Trust?

Four Hundred Stuff
Testing At iSeries Shops Not Up to Snuff, Original Finds

New PowerTech Product Cracks Down on Special Authorities

iSeries Is Center of Lean IT Operation At adidas-Salomon Canada

Four Hundred Monitor

Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc. (formerly Midrange Server), 50 Park Terrace East, Suite 8F, New York, NY 10034
Privacy Statement