LPAD

MySQL

LPAD(str, len, padstr)

• str: The string to be padded. The function will add characters to the left side of this string.
• len: The length of the resulting string after padding. If this value is less than the length of the original string, the function will trim the characters from the left.
• padstr: The string to use for padding. The function will append this string to the left of the original string until it reaches the length specified by len. If padstr is more than one character long or len is greater than the length of str, the function will repeat padstr as many times as required but will cut it short to meet the specified length.

Example

SELECT LPAD('SQL', 6, 'AB');

Output

'ABABSQL'

Explanation

In the above SQL statement, LPAD function is used to pad the left side of a string with a specific set of characters. The syntax is LPAD(str, len, padstr). Here, ‘SQL’ is the original string, 6 is the length for the resulting string, and ‘AB’ are the padding characters. Consequently, ‘ABABSQL’ is produced.

PostgreSQL

LPAD(string text, length int, fill text) RETURNS text

• string text: The source string which will undergo left-padding modification.
• length int: The total length of the output string post-modification with respective padding characters. If the given length is less than the length of the source string, the output will be truncated to the specified length.
• fill text: The string that will be used to add padding characters to the left of the source string. If omitted, spaces are used as padding characters.

Example

SELECT LPAD('123', 5, '0');

Output

00123

Explanation

The LPAD function in PostgreSQL is used to add padding to the left side of a string. The code example above takes a string ‘123’ and pads it on the left to a total length of 5 characters using the character ‘0’. As a result, the output is ‘00123’.

Oracle

LPAD(string1, n, string2)

• string1: This is the original string that needs to be padded. The LPAD function will add characters to the left of this original string.
• n: This is the final length that the padded string should have. If the original ‘string1’ is longer than ‘n’, ‘string1’ will be shortened to ‘n’ characters.
• string2: This is the string that will be used for padding. If ‘string2’ is more than one character long, it will be shortened to fit the final length ‘n’. If ‘n’ is longer than the combination of ‘string1’ and ‘string2’, ‘string2’ will be repeated until ‘n’ is reached.

Example

SELECT LPAD('SQL', 5, '*') AS Padded_String FROM dual;

Output

**SQL

Explanation

The LPAD function in Oracle is used to add padding to the left of a string. Here, the string ‘SQL’ has been padded with two asterisks ’*’ on the left to make the total string length equal to 5.

SQL Server

LPAD(string, length, pad_string)

• string: This parameter specifies the string to be padded.
• length: This parameter denotes the length to which the string should be padded. If the string is longer than this length, it gets truncated.
• pad_string: This parameter is the one with which the input string will be padded. If this parameter is omitted, the string is padded with spaces.

Example

SELECT LPAD('SQL', 5, '0') AS Padded_String;

Output

000SQL

Explanation

In the provided SQL Server example, the LPAD() function is used to pad the left side of the string ‘SQL’ with the character ‘0’ until the length of the string is 5 characters. The resulting output is ‘000SQL’.