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`][1] to keep the zeros

or

use [`LPAD()`][2]

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


[1]:

[To see links please register here]

[2]:

[To see links please register here]

Possibly:

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

<hr />

**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()`](

[To see links please register here]

).

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;

Remember, that if your value have more signs, that LPAD lenght, MySQL gonna cut it to that lenght, so you should add extra IF with legth check for that.

IF(LENGTH(column)<8,LPAD(column,8,0),column)