LFILL() allows a specified string to be padded with a specified leading character to a specified length.
Useful for harmonizing data. For instance, adding leading zeros to a key field (like an invoice number) in one table to make the key field comparable to a similar key field in another table that already contains leading zeros. Harmonizing key fields is typically done for the purpose of joining or relating the data from both tables.
LFILL(C|N, "replacement_character", length)
Takes string C and makes it the specified length. If string C is shorter than the specified length, then the string is filled to the left with the specified replacement_character to the desired length. If the string C is longer than the specified length, then the left-most characters from string are removed, to adjust the string to the desired length as required.
If a numeric value N is provided as the first function parameter, the numeric value is automatically converted to a character value with leading blanks trimmed.
Note: If the specified 'replacement-character' is a string of a length greater than 1 then only the first (i.e. leading) character is used by the function.
See also LTRIM().
Assuming a 4 character field called CUSTNO to which you wish to add 4 leading zeros to create an 8 character string:
LFILL(CUSTNO,"0",8)
This function also works very well with the LTRIM() function. For example, assume you have an 8 character field called CUSTNO containing leading zeros. If you want convert the leading zeros into leading blanks, you would:
LFILL(LTRIM(CUSTNO,"0")," ",8)