SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
9-99
LPAD Function
LPAD Function
The LPAD function replaces the leftmost specified number of characters in a character
expression with a padding character or string. With the
ANSI_STRING_FUNCTIONALITY CQD set to ON, the function pads the left side of a
character expression with the specified string.
character-expr
is an SQL character value expression. The operand is the result of evaluating
character-expr. See Character Value Expressions on page 6-41.
count
specifies the number of characters. The count must be greater than or equal to
zero of exact numeric data type and with a scale of zero. For considerations of
count based on CQD ANSI_STRING_FUNCTIONALITY, see Examples of LPAD
on page 9-99.
pad-character
specifies the padding character or a string. If no pad-character is specified,
space is the padding character. For KANJI or KSC5601, the code value of
pad-character is hexadecimal 2020.
Examples of LPAD
The behavior of the LPAD function when the ANSI_STRING_FUNCTIONALITY CQD is
set to ON and the corresponding examples are described below.
The count specifies the number of characters to be returned. It is the length of the
result string.
If count is smaller than the length of the character-expr, the character-expr is
truncated. If count is equal to the length of the character-expr, the value of the
character-expr is retained. If count is greater than the length of the
character-expr, the character-expr is left-padded with the pad-character.

The following LPAD function truncates the string 'kite' and returns two leftmost
characters 'ki':
LPAD('kite', 2)

The following LPAD function truncates the string ‘Robert John Smith’ and
returns six leftmost characters 'Robert':
LPAD('Robert John Smith', 6);
LPAD (character-expr,count [,pad-character])