CODEWITHSUNDEEP
mysql
Sinkj22
Sinkj22

Reputation: 31

How do I add leading 0's to a string in mysql?

I have a column in my table of zip codes. Some of the zip codes got truncated since they started with 0. For instance, 00123 appears as 123 and 04567 appears as 04567.

Is there a function that I can use to update all entries of the column so that if the length of the string is 3, there will be 0's place in front of the number to make it length of 5? (i.e. 123 --> 00123 & 4567 --> 04567)

Upvotes: 0

Views: 838

Answers (4)

Mike Brant
Mike Brant

Reputation: 71384

Make your zipcode field one of the text type fields. Problem solved. This makes sense when you think about it as it is unlikely that you are going to do any mathematical computations on this data. Also, this is more flexible if and when you need to accommodate countries with non-numeric postal code values.

Upvotes: 1

J. Dem
J. Dem

Reputation: 11

Try UPDATE Table SET zipCode = LPAD(zipCode, 5, '0');

This will fill your data with leading zeros. Hope that helps !

Upvotes: 0

baao
baao

Reputation: 73241

If your column already is in a string type, you can use LPAD to add leading strings:

update table set zipcode = LPAD(zipCode, 5, '0');

If it's a numeric datatype, change the column to use ZEROFILL, then do the same as above. Please note that this will automatically make your column unsigned.

See the manual

Upvotes: 2

Bernd Buffen
Bernd Buffen

Reputation: 15057

Create or ALTER the field to zerofill and set the length to that 

CREATE TABLE `abc` (
  `zip` int(5) unsigned zerofill DEFAULT NULL,
  `b` int(11) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 0

Related Questions