0

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)

Sinkj22
  • 31
  • 2
  • 4
  • 2
    Possible duplicate of [Padding the beginning of a mysql INT field with zeroes](http://stackoverflow.com/questions/5191494/padding-the-beginning-of-a-mysql-int-field-with-zeroes) – bancer Feb 15 '16 at 20:33
  • I reopened as the duplicate this was marked to dealt with display-time padding, not storage – Mike Brant Feb 15 '16 at 20:33
  • Unclear what you're asking because you haven't provided the **`datatype`** of the column. If it's *integer* type (e.g. INT), then the leading zeros will never get "stored". If you leave the column as integer, you'll need to pad with zeros (either specify zerofill attribute, or use an expression in place of the column name in the SELECT statement. If it's character type, then you can modify the contents of the column with an `UPDATE` statement, using an expression that references the current value. – spencer7593 Feb 15 '16 at 20:49

4 Answers4

2

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

baao
  • 71,625
  • 17
  • 143
  • 203
1

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.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
0

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;
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
0

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

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

J. Dem
  • 11
  • 2