SQL LPAD Function

Summary: in this tutorial, you’ll learn how to use the SQL LPAD function to pad a specified set of characters on the left of a string to a certain length.

Introduction to the SQL LPAD function #

LPAD stands for the left pad. The LPAD function lets you pad a string with specified characters on the left to a certain length.

Here’s the syntax of the LPAD function:

LPAD(string, length, pad_string)Code language: SQL (Structured Query Language) (sql)

The LPAD function takes three parameters:

• string: The input string you want to pad.
• length: The length of the resulting string after padding.
• pad_string: The string to use for padding. If you omit it, the function will use spaces by default.

The LPAD function returns a new string with the pad_string padded on the left of the input string. It ensures that the result string has a certain length.

Basic SQL LPAD function example #

The following query uses the LPAD function to pad zero on the left of the string '123' to a string with a length of 6:

SELECT LPAD('123', 6, '0') result;Code language: SQL (Structured Query Language) (sql)

Output:

 result
--------
 000123Code language: plaintext (plaintext)

The length of the string '123' is 3, so the LPAD function pads three zeros to make the length of the result string 6.

The following example uses the LPAD function to pad zeros to the left of the string '123456':

SELECT LPAD('123456', 6, '0') result;Code language: SQL (Structured Query Language) (sql)

Output:

 result
--------
 123456Code language: plaintext (plaintext)

Since the input string already has a length of 6, the result string will not have any padded zero.

Padding employee IDs #

We’ll use the employees table from the sample database:

The following query uses the LPAD function to generate a report where employee IDs are five characters long and padded with leading zeros:

SELECT 
    employee_id,
    LPAD(CAST(employee_id AS TEXT), 5, '0')  padded_employee_id
FROM 
    employees;Code language: SQL (Structured Query Language) (sql)

Output:

 employee_id | padded_employee_id
-------------+--------------------
         100 | 00100
         101 | 00101
         102 | 00102
         103 | 00103Code language: plaintext (plaintext)

How it works:

• First, cast integers to text explicitly using the CAST function.
• Second, pad the employee ID with leading zeros using the PAD function.

Summary #

• Use the SQL LPAD function to pad a string on the left with specified characters to a certain length.