lpad function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns expr, left-padded with pad to a length of len.

Syntax 

lpad(expr, len [, pad] )

Arguments

  • expr: A STRING or BINARY expression to be padded.

  • len: An INTEGER expression specifying the length of the result string

  • pad: An optional STRING or BINARY expression specifying the padding.

Returns

A STRING.

If expr is longer than len, the return value is shortened to len characters. If you do not specify pad, a STRING expr is padded to the left with space characters, whereas a BINARY expr is padded to the left with x’00’ bytes. If len is less than 1, an empty string.

BINARY is supported since: Databricks Runtime 11.0.

Examples 

> SELECT lpad('hi', 5, 'ab');
 abahi
> SELECT lpad('hi', 1, '??');
 h
> SELECT lpad('hi', 5);
    hi

> SELECT hex(lpad(x'1020', 5, x'05'))
 0505051020