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 rules apply:

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

• Specify CHAR or VARCHAR 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 rules apply:

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

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

• If a dynamic parameter is specified as the number of characters, the assumed data type of the dynamic parameter will be INTEGER type.

padding-character-string:

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

The following rules apply:

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

• You must specify CHAR or VARCHAR type data for padding-character-string.

• If padding-character-string is omitted, its assumed value is a single-byte 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 contains 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)

   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 are met, the execution result will be a null value:

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

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

4. If the actual length of the padding character string is 0 bytes or 0 characters, no character string padding will be performed.

5. If the number of characters in the target-data character string is greater than the value of num-chars, the function returns the specified number of characters from the beginning of the target-data character string.

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

6. If a character string of the specified number of characters cannot be represented in the data length of the execution result, the padding characters will be truncated in mid-string. This means that the number of characters in the execution result might be different from the specified number of characters. If you want to obtain a character string with the specified number of characters, use the scalar function CAST to change the data length of the target data.

   Examples

   These examples assume that Unicode (UTF-8) is being used as the character encoding, and that the value and data type of column C1 are as follows:

   • Value of column C1:

   • Data type of column C1: 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 of the execution result is not the specified number of characters (5).

   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 of the execution result is the specified number of characters (5).

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