MySQL adding leading numbers to existing IDs in a

Question

I got a mysql database column named country_id, like:

country_id
----------
1
2
59
435
2714

What I'm trying to accomplish now is to add leading zero's to each ID, so the result would be:

country_id
----------
0001
0002
0059
0435
2714

Each ID should have max 4 digits. That's it.

Is there any SQL statement I could use in PHPmyAdmin to update this country_id column in the way described above?

Best regards!

Answer 1

If country_id column is character datatype (NOT numeric), then you could prepend zeros and take the rightmost portion:

UPDATE foo SET country_id = RIGHT(CONCATENATE('0000',country_id),4)

UPDATE foo SET country_id = LPAD(country_id, 4, '0')

(NOTE: Either of those statements will result in data loss, if any value of country_id is longer than 4 characters... the first gets the righmost characters, the second will rth get the four leftmost characters, if country_id is over four characters. If a value has e a leading '-' character, that will result in an odd looking value e.g. '00-4'. The LTRIM function is available to remove leading spaces.

---

If, on the other hand, country_id is a numeric datatype, then you can't really add leading zeros.

For an integer type, you can specify a display length and the ZEROFILL option, e.g.

country_id INT(4) ZEROFILL

But it's up to the client application to make use of the length modifier and the ZEROFILL attribute to do the specified formatting, there's not really anything being done "in the database" with those attributes. (The MySQL command line client will observe those settings, and display the value zero filled to a max of four characters. But other clients are not required to do that.)

You could also cast that value to character and pad it with leading '0' characters, in a query:

SELECT LPAD(country_id, 4, '0') AS country_id

But note that's going to return a character type, not a numeric.

Answer 2

SELECT LPAD(country_id, 4, '0')
FROM your_table

If you can change your DB structure use ZEROFILL

Answer 3

Declare ZEROFILL on the column:

mysql> create table foobar (nr integer(4) ZEROFILL);
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO foobar VALUES (1),(12),(123),(1234),(12345);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM foobar;
|  0001 
|  0012 
|  0123 
|  1234 
| 12345