Spark SQL - Left and Right Padding (lpad and rpad) Functions

Similar as many database query engines, Spark SQL also supports lpad and rpad functions to pad characters at the beginning or at the end of a string.

Syntax

lpad(str, len[, pad]) - Returns str, left-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters or bytes. If pad is not specified, str will be padded to the left with space characters if it is a character string, and with zeros if it is a byte sequence.

lpad(str, len[, pad]) - Returns str, left-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters or bytes. If pad is not specified, str will be padded to the left with space characters if it is a character string, and with zeros if it is a byte sequence.

Code snippet

The following examples pad string to 23 characters.

spark-sql> select lpad('123456789',23,'0');
00000000000000123456789

spark-sql> select rpad('123456789',23,'X');
123456789XXXXXXXXXXXXXX

spark-sql> select rpad('123456789',23);
123456789