LPAD

Pads a expr string on the left with characters or strings from the pad argument to the length len.

Syntax

LPAD(expr, len [, pad])

Arguments

expr: The value of the STRING data type.

---

len: The total length of the result as a numeric argument or an argument of another data type that can be converted to a numeric data type, which can be evaluated as an integer. In some multibyte character sets, the display length of a string may differ from len.

---

pad (optional): The value of the STRING data type. Characters/bytes from this parameter are used to pad the base.

Return Values

The data type of the return value is STRING.

Usage Notes

• If expr is longer than len, expr is truncated to the length len.

• The pad can be multiple characters/bytes long. The pad argument is repeated in the result until the desired length len is reached. Any excess characters/bytes in the pad argument are truncated.

• If expr is a string and pad is not specified, ' ' (a single space) is used.

Example 1

SELECT  col1, 
        lpad(col1, 7, ' ') as result1,  
        length(lpad(col1, 7, ' ')) as result1_len,   
        lpad(col1, 13, '@') as result2,
        length(lpad(col1, 13, '@')) as result2_len,
        lpad(col1, 13, 'ab') as result3,
        length(lpad(col1, 13, 'ab')) as result3_len
        FROM (
                select           'Lorem'       as col1
                union all select 'ipsum dolor' as col1
                union all select '7391.91'     as col1);

+-------------+---------+-------------+---------------+-------------+---------------+-------------+
| COL1        | RESULT1 | RESULT1_LEN | RESULT2       | RESULT2_LEN | RESULT3       | RESULT3_LEN |
+-------------+---------+-------------+---------------+-------------+---------------+-------------+
| Lorem       | Lorem   | 7           | @@@@@@@@Lorem | 13          | ababababLorem | 13          |
| ipsum dolor | ipsum d | 7           | @@ipsum dolor | 13          | abipsum dolor | 13          |
| 7391.91     | 7391.91 | 7           | @@@@@@7391.91 | 13          | ababab7391.91 | 13          |
+-------------+---------+-------------+---------------+-------------+---------------+-------------+