Function Reference > Functions > LPAD

LPAD

Adds a set of blanks or characters to the beginning of a string to set the string to a specified length.

Syntax

LPAD( first_string, length [,second_string] )

Argument	Required/ Optional	Description
first_string	Required	Can be a character string. The strings you want to change. You can enter any valid expression.
length	Required	Must be a positive integer literal. This argument specifies the length you want each string to be. When length is a negative number, LPAD returns NULL.
second_string	Optional	Can be any string value. The characters you want to append to the left-side of the first_string values. You can enter any valid expression. You can enter a specific string literal. However, enclose the characters you want to add to the beginning of the string within single quotation marks, as in 'abc'. This argument is case sensitive. If you omit the second_string, the function pads the beginning of the first string with blanks.

Return Value

String of the specified length.

NULL if a value passed to the function is NULL or if length is a negative number.

Example

The following expression standardizes numbers to six digits by padding them with leading zeros.

LPAD( PART_NUM, 6, '0')

PART_NUM	RETURN VALUE
702	000702
1	000001
0553	000553
484834	484834

LPAD counts the length from left to right. If the first string is longer than the length, LPAD truncates the string from right to left. For example, LPAD(‘alphabetical’, 5, ‘x’) returns the string ‘alpha’.

If the second string is longer than the total characters needed to return the specified length, LPAD uses a portion of the second string:

LPAD( ITEM_NAME, 16, '*..*' )

ITEM_NAME	RETURN VALUE
Flashlight	..*.Flashlight
Compass	....*Compass
Regulator System	Regulator System
Safety Knife	..Safety Knife

The following expression shows how LPAD handles negative values for the length argument for each row in the ITEM_NAME column:

LPAD( ITEM_NAME, -5, '.')

ITEM_NAME	RETURN VALUE
Flashlight	NULL
Compass	NULL
Regulator System	NULL