CODEWITHSUNDEEP
phpmysqlsqlzipcode
user1032531
user1032531

Reputation: 26311

Forcing MySQL CHAR() column to require all digits or auto left pad with zeros

I am designing a table which stores 5 digit US zipcodes without extensions. It looks like the leading contenders are CHAR(5) and MEDIUMINT (5) UNSIGNED ZEROFILL. Reference:

I plan on going with CHAR(5) for reasons described by Is it a good idea to use an integer column for storing US ZIP codes in a database?. EDIT-As such, using ZEROFILL is not an answer, so please do not ask me to use ZEROFILL or close this question and refer to a previous answer which says to use ZEROFILL.

Next, I want to ensure that (5) characters are always stored, and if less are provided, then either the query produces an error, or it is left padded with zeros (and not right-padded with spaces). My intent is to prevent zipcode "3101" from ever existing in the database, and ensure that it must be "03101". Note that I am not asking how to make a PHP application left pad the zipcodes with zeros.

Upvotes: 0

Views: 1222

Answers (3)

Tom
Tom

Reputation: 6663

You can use the LPAD function to left pad a value with zeroes.

LPAD (zip_code, 5, '0')

Upvotes: 2

Buzut
Buzut

Reputation: 5173

First of all, you'd better use a SMALLINT than a CHAR if you plan to save digits only. INT and its derivatives are made for that : - ti will take less space - and it will go fatser to read/write (even if the difference is tiny)

As to be sure your that you always have 5 characters, you have to use ZEROFILL. You should have a look at this thread : What is the benefit of zerofill in MySQL?

Upvotes: 0

todd
todd

Reputation: 322

If you add the zerofill attribute to the field and set the lenth to 5 it will pad left with a zero as stated in the link you provided.

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `test` int(5) unsigned zerofill DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Just be sure to validate the zip before entering it.

Also sometimes zipcodes have a 4 digit suffix. Might want to make it a char(10) if you expect you might have some zipcodes in this format. Or a zip_suffix column.

Upvotes: 0

Related Questions