MySQL - 如何前面填充邮件代码与“0”? [英] MySQL - how to front pad zip code with "0"?
问题描述
在我的MySQL InnoDB数据库中,我有脏邮政编码数据,我想清理。
In my MySQL InnoDB database, I have dirty zip code data that I want to clean up.
干净的邮政编码数据是当我有所有5位数邮政编码(例如90210)。
The clean zip code data is when I have all 5 digits for a zip code (e.g. "90210").
但是由于某种原因,我在数据库中发现,对于以0开头的邮政编码,0
But for some reason, I noticed in my database that for zipcodes that start with a "0", the 0 has been dropped.
因此, Holtsville,New York 包含邮政编码 00544
存储在我的数据库中作为 544
So "Holtsville, New York" with zipcode "00544
" is stored in my database as "544
"
和
Dedham,MA 与邮政编码 02026
存储在我的数据库中为 2026
。
"Dedham, MA" with zipcode "02026
" is stored in my database as "2026
".
什么SQL可以运行到前面的0到任何长度不是5位数字的邮政编码?意思是,如果邮政编码的长度为3位数字,前面的00。如果邮政编码长度为4位数,则前面只能填0。
What SQL can I run to front pad "0" to any zipcode that is not 5 digits in length? Meaning, if the zipcode is 3 digits in length, front pad "00". If the zipcode is 4 digits in length, front pad just "0".
UPDATE :
我把邮政编码改为数据类型VARCHAR(5)
I just changed the zipcode to be datatype VARCHAR(5)
推荐答案
而不是数字类型,或者当您从DB加载它时,您的应用程序用零填充。使用php执行此操作的一种方法:
Store your zipcodes as CHAR(5) instead of a numeric type, or have your application pad it with zeroes when you load it from the DB. A way to do it with php:
echo sprintf("%05d", 205); // prints 00205
echo sprintf("%05d", 1492); // prints 01492
或者你可以用MySQL为你填充:
Or you could have MySQL pad it for you:
SELECT LPAD(zip, 5, '0') as zipcode FROM table;
以下是更新和填充所有行的方法:
Here's a way to update and pad all rows:
ALTER TABLE `table` CHANGE `zip` `zip` CHAR(5); #changes type
UPDATE table SET `zip`=LPAD(`zip`, 5, '0'); #pads everything
这篇关于MySQL - 如何前面填充邮件代码与“0”?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!