8.5.3 LPAD

(1) Specification format

scalar-function-LPAD ::= LPAD(target-data,num-chars[,padding-character-string])
 
  target-data ::= value-expression
  num-chars ::= value-expression
  padding-character-string ::= value-expression

To Page Top

(2) Explanation of specification format

target-data:

Specifies the character string data to be padded.

The following specification rules apply:

• Specify the target data in the form of a value expression. For details about value expressions, see 7.21 Value expression.

• Specify CHAR, VARCHAR, or STRING type data for the target data.

• You cannot specify a dynamic parameter by itself for the target data.

num-chars:

Specifies the number of characters in the result character string after it is padded.

The following specification rules apply:

• Specify the number of characters in the form of a value expression. For details about value expressions, see 7.21 Value expression.

• Specify data of type INTEGER, BIGINT, or SMALLINT for the number of characters.

• If you specify a dynamic parameter alone for the number of characters, the assumed data type of the dynamic parameter is BIGINT type^#.

  #

  If the integer data type format is a legacy format, it will be an INTEGER type.

padding-character-string:

Specifies the character string to be used for padding the beginning (left side) of the target data.

The following specification rules apply:

• Specify padding-character-string in the form of a value expression. For details about value expressions, see 7.21 Value expression.

• Specify CHAR, VARCHAR, or STRING type data for the padding-character-string.

• If padding-character-string is omitted, its assumed value is a half-width space character.

• If a dynamic parameter is specified by itself for padding-character-string, the assumed data type of the dynamic parameter is VARCHAR(32000).

The following example illustrates the result of executing the scalar function LPAD.

Example:

Pad the beginning (left side) of the data in column C1 with the character string 'xyz' repeatedly until the data has a total length of 10 characters.

LPAD("C1",10,'xyz') → 'xyzxyzxABC'

Column C1 has type VARCHAR(20) and holds the character string 'ABC'.

To Page Top

(3) Rules

1. The data type and data length of the execution result are shown in the following table.

   Table 8‒13: Data type and data length of the execution result of the scalar function LPAD

   Data type and data length of the target data	Data type and data length of the execution result
   CHAR(n)	VARCHAR(n)
   VARCHAR(n)
   STRING	STRING

   Legend: n: Maximum length of the target data

2. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

3. If either of the following conditions is met, the execution result will be a null value.

   • If the target data, number of characters, or padding character string is a null value

   • When you specify a negative value for the number of characters

4. If the padding character string is data with an actual length of 0 bytes or 0 characters, the character string is not padded.

5. If the number of characters in the target-data is greater than the character count value, a character string of the target-data is returned for the specified number of characters from the beginning.

   Example: LPAD('ABCDE',3,'xy') → 'ABC'

6. If the data length of the execution result cannot represent the specified number of characters, padding of the padding character string is terminated halfway through. Therefore, the number of characters in the execution result may differ from the specified number of characters. To obtain a character string of a specified number of characters, use the scalar function CAST to change the data length of the target data.

   Example:

   The values and data types of column C1 are as follows, and the character encoding used is Unicode (UTF-8).

   • C1 column value:

   • C1 column data type: VARCHAR(10)

   LPAD("C1",5,' ') → ' '

   In the above example, the data type of the execution result of LPAD is VARCHAR(10). Because each character is 3 bytes, the number of characters in the result will not be the specified number of characters (5 characters).

   LPAD(CAST("C1" AS VARCHAR(15)),5,' ') → ' '

   In the above example, the data type of the execution result of LPAD is VARCHAR(15). Because each character is 3 bytes, the number of characters in the execution result will be the specified number of characters (5 characters).

To Page Top

(4) Example

Example:

Column C1 of table T1 is a column of the VARCHAR(8) type. In column C1, for each row containing a character string shorter than 8 characters, add an appropriate number of 0s to the left of each character string so that all rows in the column contain an 8-character string.

SELECT LPAD("C1",8,'0') FROM "T1"

To Page Top