ZONED( ) function

Converts numeric data to character data and adds leading zeros to the output.

Syntax

ZONED(number, length)

Parameters

Name Type Description
number

numeric

The numeric value to convert to a string.

Note

If you want to add leading zeros to a character value that contains a numeric string, you must use the VALUE( ) function to convert the character to the numeric data type before using the value as input for ZONED( ). For more information, see VALUE( ) function.
length numeric

The length of the output string.

Output

Character.

Examples

Basic examples

Integer input

Returns "235":

ZONED(235, 3)

Returns "00235", because length is greater than the number of digits in number so two leading zeros are added to the result:

ZONED(235, 5)

Returns "35", because length is less than the number of digits in number so the leftmost digit is truncated from the result:

ZONED(235, 2)

Decimal input

Returns "23585", because the zoned data format does not support a decimal point:

ZONED(235.85, 5)

Negative input

Returns "64489M", because the number is negative and "M" represents the final digit 4:

ZONED(-6448.94, 6)

Returns "489J", because length is less than the number of digits in number so the two leftmost digits are truncated from the result, and the number is negative and "J" represents the final digit 1:

ZONED(-6448.91, 4)

Advanced examples

Adding leading zeros to a character field containing numbers

The Employee_Number field contains the value "254879". You need to convert the value to a 10-digit string with leading zeros.

Tip

You must use the VALUE( ) function to convert the character to numeric data before using the numeric as input for ZONED( ).

COMMENT returns "0000254879"
ASSIGN v_str_length = 10
ASSIGN v_num_decimals = 0
ZONED(VALUE(Employee_Number, v_num_decimals), v_str_length)

Harmonizing a key field when joining tables

You have two tables, Ar and Customer, and you need to join them on the CustNo field for further analysis. The two tables each have a CustNo field, but the data format is different:

  • Ar numeric field (for example, 235)
  • Customer 5 character field that pads numbers with leading zeros (for example, "00235")

To harmonize the fields when joining so that the data types and lengths are equal, you use the ZONED( ) function to convert the Ar key field CustNo to a character field of length 5 so that it matches the format of the key field in Customer:

OPEN Ar PRIMARY
OPEN Customer SECONDARY
JOIN PKEY ZONED(CustNo,5) FIELDS CustNo Due Amount SKEY CustNo UNMATCHED TO Ar_Cust OPEN PRESORT SECSORT

Remarks

How it works

This function converts numeric data to character data and adds leading zeros to the output. The function is commonly used to harmonize fields that require leading zeros, for example, check number, purchase order number, and invoice number fields.

When to use ZONED( )

Use the function to convert a positive numeric value to a character value containing leading zeros. This is useful for normalizing data in fields to be used as key fields.

For example, if one table contains invoice numbers in the form 100 in a numeric field, and another table contains invoice numbers in the form "00100" in a character field, you can use ZONED( ) to convert the numeric value 100 to the character value "00100". This allows you to compare like invoice numbers.

String length and return values

Leading zeros are added to the output value when the length value is more than the number of digits in number. When length is less than the number of digits in number, the output is truncated from the left side. If the number value is the same length as length, then no zeros are added.

Decimal numbers

The zoned data format does not include an explicit decimal point.

Negative numbers

If the input number is negative, the rightmost digit is displayed as a character in the result:

  • "}" for 0
  • a letter between "J" and "R" for the digits 1 to 9

ZONED( ) and the Unicode edition of Analytics

If you are working with the Unicode edition of Analytics, you need to use the BINTOSTR( ) function to correctly display the return value of the ZONED( ) function. You also need to use the BINTOSTR( ) function if you want to use the return value of the ZONED( ) function as a parameter in another function.

[ Back to top ]

ACL Scripting Guide 14.1