MySQL LPAD() With Leading Zeros

In SQL language, the LPAD() function allows to complete a string until it reaches the desired size, by adding characters at the beginning of this string.

The LPAD() function is used with 2 or 3 parameters. The first parameter is the input string, the second parameter defines the desired length. Finally, the third parameter is the fill string. This last parameter is optional on some Database Management Systems (DBMS) such as Oracle or PostgreSQL (but not for MySQL).

Important: if the desired length is smaller than the actual size of the string, then the output string will be truncated to make the exact size that is desired.
 

 

Syntax:

This SQL function can be used simply in SQL queries, as shown in the following queries:

SELECT LPAD('StackHowTo', 5, 'x');  -- output 'Stack'
SELECT LPAD('StackHowTo', 10, 'x');  -- output 'StackHowTo'
SELECT LPAD('StackHowTo', 15, 'x'); -- output 'xxxxxStackHowTo'
SELECT LPAD('StackHowTo', 14, 'xy'); -- output 'yxyxStackHowTo'
SELECT LPAD('123', 6, '0');     -- output '000123'

 

Example :

Let’s suppose that a computer system has a database of products. A “product” table could contain the price of products in a format without commas, such as “14,80$” could be written “1480” in the database. This example is purely a dummy and will illustrate the advantage of the LPAD() function.
 
Products table:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | Monitor  | computer   |  45   | 1480    |
|  102 | CD-ROM   | computer   |  100  | 2280    |
|  103 | Modem    | computer   |  12   | 2109    |
|  104 | Speaker  | computer   |  11   | 1111    |
+------+----------+------------+-------+---------+

 

 
Some old computer systems require you to format the results in a rather precise way. Let’s suppose that we want to display the price in 8 characters, with zeros at the beginning of the price. To display the price in such a format you can use the following SQL query:

SELECT id, name, category, stock, price_with_format, LPAD(price_with_format, 8, '0')
FROM products

Output:

+------+----------+------------+-------+---------+---------------------------------+
|  id  |   name   | category   | stock |  price  | LPAD(price_with_format, 8, '0') |
+------+----------+------------+-------+---------+---------------------------------+
|  101 | Monitor  | computer   |  45   | 1480    | 00001480                        |
|  102 | CD-ROM   | computer   |  100  | 2280    | 00002280                        |
|  103 | Modem    | computer   |  12   | 2109    | 00002109                        |
|  104 | Speaker  | computer   |  11   | 1111    | 00001111                        |
+------+----------+------------+-------+---------+---------------------------------+

This result shows that the price can be formatted in the desired way using the LPAD() function. It is not hard for a computer program to remove the initial zeros to get the price back in a more usable format.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More