Adding a leading zero to some values in column in MySQL

I have a CSV file sent to me in CSV. The field of interest is 8 digits. Some of those started with a 0. The field was sent numeric. So, I now have dropped some leading zeros.

I already converted the field to varchar. I now need to do this:

I have this now:

12345678
1234567

I need to have this:

12345678
01234567

Change the field back to numeric and use ZEROFILL to keep the zeros

or

use LPAD()

SELECT LPAD('1234567', 8, '0');

Possibly:

select lpad(column, 8, 0) from table;

---

Edited in response to question from mylesg, in comments below:

> ok, seems to make the change on the query- but how do I make it stick (change it) permanently in the table? I tried an UPDATE instead of SELECT

I'm assuming that you used a query similar to:

UPDATE table SET columnName=lpad(nums,8,0);

If that was successful, but the table's values are still without leading-zeroes, then I'd suggest you probably set the column as a numeric type? If that's the case then you'd need to alter the table so that the column is of a text/varchar() type in order to preserve the leading zeroes:

First:

ALTER TABLE `table` CHANGE `numberColumn` `numberColumn` CHAR(8);

Second, run the update:

UPDATE table SET `numberColumn`=LPAD(`numberColum`, 8, '0');

This should, then, preserve the leading-zeroes; the down-side is that the column is no longer strictly of a numeric type; so you may have to enforce more strict validation (depending on your use-case) to ensure that non-numerals aren't entered into that column.

References:

• lpad().

I had similar problem when importing phone number data from excel to mysql database. So a simple trick without the need to identify the length of the phone number (because the length of the phone numbers varied in my data):

UPDATE table SET phone_num = concat('0', phone_num) 

I just concated 0 in front of the phone_num.

A previous answer using LPAD() is optimal. However, in the event you want to do special or advanced processing, here is a method that allows more iterative control over the padding. Also serves as an example using other constructs to achieve the same thing.

UPDATE
	mytable
SET
	mycolumn = CONCAT(
		REPEAT(
			"0",
			8 - LENGTH(mycolumn)
		),
		mycolumn
	)
WHERE
	LENGTH(mycolumn) < 8;