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

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• How to remove -XXX from Zip Code field using MySQL REGEXP?
• Validate the ZIP code with Java Regular expressions
• Zip Code validation using Java Regular Expressions
• In MySQL, how can we pad a string with another string?
• Pad the values of the column with zeros in MySQL
• Replace 0 with null in MySQL?
• How to pad a number with leading zeros in JavaScript?
• How to update multiple rows and left pad values in MySQL?
• Working with zip files in Python
• How to pad the input tensor boundaries with zero in PyTorch?
• C++ code to check query for 0 sum
• Python code to move spaces to the front of string in a single traversal
• How do you fill in or pad a column with zeros using a MySQL query?
• How to add comments in MySQL Code?
• Query MySQL with unicode char code?