Add Leading Zero to Values in a MySQL Column

To add leading zero to some value, use the LPAD() function of MySQL. The syntax is as follows −

select lpad(yourColumnName, lengthofColumnValue+1,0) from yourTableName;

Here is an example of LPAD().

mysql> select lpad('98765432',9,0);

The following is the output −

+----------------------+
| lpad('98765432',9,0) |
+----------------------+
| 098765432            |
+----------------------+
1 row in set (0.00 sec)

To check it in a live example, let us first create a table −

mysql> create table leadingZeroDemo
−> (
   −> Id varchar(200)
−> );
Query OK, 0 rows affected (0.63 sec)

Now insert some records into the table with the help of insert command. The query is as follows −

mysql> insert into leadingZeroDemo values('2345');
Query OK, 1 row affected (0.17 sec)

mysql> insert into leadingZeroDemo values('1234');
Query OK, 1 row affected (0.16 sec)

mysql> insert into leadingZeroDemo values('9876');
Query OK, 1 row affected (0.14 sec)

mysql> insert into leadingZeroDemo values('4321');
Query OK, 1 row affected (0.13 sec)

Display how many records are present in the table. The query to display all records.

mysql> select *from leadingZeroDemo;

The following is the output −

+------+
| Id   |
+------+
| 2345 |
| 1234 |
| 9876 |
| 4321 |
+------+
4 rows in set (0.00 sec)

Apply the LPAD() function to add leading zero. The query to add leading zero is all follow −

mysql> select lpad(Id,5,0) from leadingZeroDemo;

The following is the output that adds leading zero −

+--------------+
| lpad(Id,5,0) |
+--------------+
| 02345        |
| 01234        |
| 09876        |
| 04321        |
+--------------+
4 rows in set (0.00 sec)