How to use the LPAD() function in SQL

The LPAD() function

The LPAD() function in SQL adds a substring to a string of a certain length. It fills in the substring from the left-hand side.

Syntax

LPAD(string,length,fill_string)

Parameter

  • string: This represents the string that is filled up by the substring.
  • length: This represents the length of the string after it has been filled up by the substring.
  • fill_string: This represents the substring that fills up the string to the length.

Example

Let’s assume we have a table containing our staff’s record. We are given a task to add the company’s abbreviated name to the ID of each staff.

We can accomplish this task using the LPAD() function.

The following code demonstrates how to use the LPAD() function in SQL.

CREATE TABLE Employee (
    id int,
    first_name varchar(50),
    last_name varchar (50),
    salary int,
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Employee
VALUES (01,'Sharon', 'Peller',40000,'Female','Kogi');
INSERT INTO Employee
VALUES (02,'Paul', 'Dons',150000,'Male','Lagos');
INSERT INTO Employee
VALUES (03,'Ameera', 'Abedayo',200000,'Female','Imo');
INSERT INTO Employee
VALUES (04,'Maria', 'Elijah',320000,'Female','Lagos');
INSERT INTO Employee
VALUES (05,'David', 'Hassan',250000,'Male','Abuja');
INSERT INTO Employee
VALUES (06,'Niniola', 'Disu',80000,'Female','Lagos');
INSERT INTO Employee
VALUES (08,'Joe', 'Smith',75000, 'Male','Lagos');
-- Query 
SELECT LPAD(cast(id AS varchar),7, 'EDT-AN' ) AS new_id, first_name, last_name
FROM Employee;

Explanation

  • Lines 1–7: We create a table called Employee which has the columns id, name, salary, gender, and state.
  • Lines 11–24: We add data into the Employee table.
  • Lines 27–28: First, we cast the id column to a string type. Then, we use the LPAD() function to add the substring "EDT-AN" to each id string with a specific length.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)