LPAD Function in SQL

This string function adds the symbol or the string to the left side of the original string. In Structured Query Language, we can use this function on both string and numbers.

Syntax of LPAD String Function

Syntax1: This syntax uses the LPAD function with the column name of the SQL table:

In this syntax, Column_Name is the name of column whose values are to be padded, size is the total length of column value after padded, and lpad_string is that string which is to be added on the left side.

Syntax2: This syntax uses the LPAD function with the set of characters (string):

In this syntax, String is a value which is to be padded, size is the total length of column value after padded, and lpad_string is that string which is to be added on the left side of given original string.

Examples of LPAD String function

Example 1: The following SELECT query adds the # symbol three times to the left of the NEW string:

Output:

###NEW  

Example 2: The following SELECT query adds the ‘Good’ string to the left of the given original string:

Output:

Good TutorAspire 

Example 3: The following SELECT query adds the 9 number four times to the left of given number 8:

Output:

99998  

Example 3: The following SELECT query adds the xy string to the left of given character z:

Output:

xyz  

Example 5: This example uses the LPAD function with the SQL table

In this example, we are going to create a new table, which is used with the LPAD function.

The syntax for creating the new table in the SQL database is as follows:

The following CREATE statement creates the Faculty_Info table:

The below INSERT queries insert the records of college Faculties in the Faculty_Info table:

The following SELECT statement displays the inserted records of the above Faculty_Info table:

The following SELECT query uses the LPAD function with the Faculty_Last_Name column of the above Faculty_Info table:

This SELECT statement adds the * symbol to the left of Last_Name of each faculty:

Output:

The following SELECT query uses the LPAD function with the Faculty_First_Name, Faculty_City, and Faculty_Address columns of those faculties whose faculty_Id is greater than 1002 in the above Faculty_Info table:

Output:

The following SELECT query uses the LPAD function with the Faculty_Salary column of the above Faculty_Info table:

This SELECT statement adds the 5 number to the left of salary of each faculty:

Output: