Use the LPAD function to format strings - MaxCompute - Alibaba Cloud Documentation Center

If you need to format strings in a table to ensure consistency and alignment of output strings, MaxCompute provides the LPAD function for you to left-pad the string str1 with the string str2 to a specified length. This function is an additional function of MaxCompute V2.0. This topic describes the syntax of the LPAD function and provides examples of using the LPAD function.

Syntax

string lpad(string <str1>, int <length>, string <str2>)

Parameters

str1: required. A value of the STRING type. This parameter specifies the string that you want to left pad.
length: required. A value of the INT type. This parameter specifies the total number of characters after left padding.
str2: required. This parameter specifies the string that you use to left pad another string.

Return value

A value of the STRING type is returned. The return value varies based on the following rules:

If the value of length is less than the number of characters in str1, this function truncates str1 from the left to obtain a string with the number of characters specified by length.
If length is set to 0, an empty string is returned.
If no input parameters are present or an input parameter is set to null, null is returned.
If the value of length is greater than the number of characters in str1 and less than the total number of characters in str1 and str2, this function left-pads str1 with str2 to the specified length.

Examples

Example 1: Left-pad the string abcdefgh with the string 12 to obtain a string with 10 characters in length. Sample statement:
```
-- The return value is 12abcdefgh. 
SELECT lpad('abcdefgh', 10, '12');
```
Example 2: Left-pad the string abcdefgh with the string 12 to obtain a string with 5 characters in length. Sample statement:
```
-- The return value is abcde. 
SELECT lpad('abcdefgh', 5, '12');
```

Example 3: The value of length is 0. Sample statement:

-- The return value is an empty string. 
SELECT lpad('abcdefgh' ,0, '12');

Example 4: An input parameter is set to null. Sample statement:
```
-- The return value is null. 
SELECT lpad(null ,0, '12');
```
Example 5: Left-pad the string abcdefgh with the string 12 to obtain a string with 9 characters in length. Sample statement:
```
-- The return value is 1abcdefgh.
SELECT lpad('abcdefgh',9,'12');
```

Related functions

LPAD is a string function. For more information about functions related to string searches and conversion, see String functions.