How to front pad zip code with “0” in MySQL?

To front pad zip code with 0, use LPAD() function in MySQL. The syntax is as follows −

SELECT LPAD(yourColumnName, columnWidth+1, '0') as anyVariableName from yourTableName;

To understand the above concept of LPAD() to add front pad zip code with 0, let us create a table. One of the columns of the table is Zip Code. The following is the query to create a table.

mysql> create table ZipCodePadWithZeroDemo
   −> (
   −> Name varchar(200),
   −> YourZipCode int(6)
   −> );
Query OK, 0 rows affected (0.44 sec)

Insert some records in the table. The query to insert records is as follows −

mysql> insert into ZipCodePadWithZeroDemo values('John',23455);
Query OK, 1 row affected (0.13 sec)

mysql> insert into ZipCodePadWithZeroDemo values('Carol',46523);
Query OK, 1 row affected (0.62 sec)

mysql> insert into ZipCodePadWithZeroDemo values('Johnson',12345);
Query OK, 1 row affected (0.11 sec)

mysql> insert into ZipCodePadWithZeroDemo values('David',34567);
Query OK, 1 row affected (0.18 sec)

Display all records from the table. The query is as follows −

mysql> select *from ZipCodePadWithZeroDemo;

The following is the output −

+---------+-------------+
| Name    | YourZipCode |
+---------+-------------+
| John    | 23455       |
| Carol   | 46523       |
| Johnson | 12345       |
| David   | 34567       |
+---------+-------------+
4 rows in set (0.00 sec)

Implement the syntax which we discussed in the beginning to add front pad zip code with value 0. The query is as follows −

mysql> SELECT LPAD(YourZipCode, 6, '0') as UPDATEDZIPCODE from ZipCodePadWithZeroDemo;

We will also get the name here −

mysql> SELECT Name,LPAD(YourZipCode, 6, '0') as UPDATEDZIPCODE from ZipCodePadWithZeroDemo;

The following output displays the Name as well as the Zip Code −

+---------+----------------+
| Name    | UPDATEDZIPCODE |
+---------+----------------+
| John    | 023455         |
| Carol   | 046523         |
| Johnson | 012345         |
| David   | 034567         |
+---------+----------------+
4 rows in set (0.00 sec)

Anvi Jain

Published on 10-Jan-2019 13:01:44

Previous Page Print Page