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 dataINSERT INTO EmployeeVALUES (01,'Sharon', 'Peller',40000,'Female','Kogi');INSERT INTO EmployeeVALUES (02,'Paul', 'Dons',150000,'Male','Lagos');INSERT INTO EmployeeVALUES (03,'Ameera', 'Abedayo',200000,'Female','Imo');INSERT INTO EmployeeVALUES (04,'Maria', 'Elijah',320000,'Female','Lagos');INSERT INTO EmployeeVALUES (05,'David', 'Hassan',250000,'Male','Abuja');INSERT INTO EmployeeVALUES (06,'Niniola', 'Disu',80000,'Female','Lagos');INSERT INTO EmployeeVALUES (08,'Joe', 'Smith',75000, 'Male','Lagos');-- QuerySELECT LPAD(cast(id AS varchar),7, 'EDT-AN' ) AS new_id, first_name, last_nameFROM Employee;
Explanation
- Lines 1–7: We create a table called
Employeewhich has the columnsid,name,salary,gender, andstate. - Lines 11–24: We add data into the
Employeetable. - Lines 27–28: First, we cast the
idcolumn to a string type. Then, we use theLPAD()function to add the substring"EDT-AN"to eachidstring with a specific length.
TRENDING TOPICS
