LEADINGZEROS( ) function

Adds leading zeros to a character string or a number.

Syntax

LEADINGZEROS(string/number, length)

Parameters

Name	Type	Description
string/number	character numeric	The field, expression, or literal value to add leading zeros to. Leading and trailing spaces are automatically trimmed from character values, and from the result of character expressions, before the leading zeros are added.
length	numeric	The length of the output string. Note Any value in string/number longer than length is truncated from the left.

Name

Type

Description

string/number

character

numeric

The field, expression, or literal value to add leading zeros to.

Leading and trailing spaces are automatically trimmed from character values, and from the result of character expressions, before the leading zeros are added.

length

numeric

The length of the output string.

Note

Any value in string/number longer than length is truncated from the left.

Output

Character.

Examples

Basic examples

Character input

Returns "000235", because length is greater than the number of characters in string/number so three leading zeros are added to the result:

LEADINGZEROS("235", 6)

Returns "35", because length is less than the number of characters in string/number so the result is truncated from the left:

LEADINGZEROS("235", 2)

Integer input

Returns "235":

LEADINGZEROS(235, 3)

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

LEADINGZEROS(235, 5)

Decimal input

Returns "023585", because LEADINGZEROS( ) removes the decimal point:

LEADINGZEROS(235.85, 6)

Negative input

Returns "0644894", because LEADINGZEROS( ) removes the negative sign:

LEADINGZEROS(-6448.94, 7)

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.

COMMENT returns "0000254879"
ASSIGN v_str_length = 10
LEADINGZEROS(Employee_Number, v_str_length)

Harmonizing a key field when relating tables

You have two tables, Ar and Customer, and you plan to relate them on the customer number key field for further analysis. However, the two key fields have different data formats, which prevents values from matching:

Table	Key field	Data type	Field length	Example
Ar	CustNum	Numeric	4	4455
Customer	CustNo	Character	6 (pads numbers with leading zeros)	"004455"

Table

Key field

Data type

Field length

Example

CustNum

Numeric

4455

Customer

CustNo

Character

(pads numbers with leading zeros)

"004455"

To harmonize the fields when relating, you create a computed field in the Ar table that uses the LEADINGZEROS( ) function. You then relate using the computed field:

OPEN Customer
INDEX ON CustNo TO Customer_on_CustNo
OPEN Ar
COMMENT Create the computed field CustNum_Zeros that converts the CustNum field to the character data type and adds leading zeros.
DEFINE FIELD CustNum_Zeros COMPUTED LEADINGZEROS(CustNum,6)					
COMMENT Relate the Ar table using the newly created CustNum_Zeros computed field.
DEFINE RELATION CustNum_Zeros WITH Customer INDEX Customer_on_CustNo

Remarks

How it works

This function adds leading zeros to the output if the output length you specify is greater than the length of an input value. The function accepts various kinds of character and numeric input and outputs a character string. The function works the same way in the Unicode and non-Unicode editions of Analytics.

The function is commonly used to normalize fields that require leading zeros, for example, check number, purchase order number, and invoice number fields.

Input length and output values

Input length	Output values
string/number less than length	leading zeros added
string/number equal to length	no zeros added
string/number greater than length	values truncated from the left

Negative sign, thousands separator, and decimal point

Input values	Result
Numeric	LEADINGZEROS( ) removes the negative sign, the thousands separator, and the decimal point from numeric input values. The removed symbols are not included in the length of the input value.
Character	LEADINGZEROS( ) does not remove the negative sign, the thousands separator, or the decimal point from character input values. The removed symbols are included in the length of the input value.

Input values

Result