Adding a leading zero to some values in column in MySQL

Question

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

Resht

Answer 1

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

or

use LPAD()

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

Tuberculin

Keep an eye on the ZEROFILL. "As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types. You should expect support for ZEROFILL and display widths for integer data types to be removed in a future version of MySQL." - dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html – Troy

Answer 2

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().

Raster

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. – Resht

Answer 3

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.

Octagonal

no, because depending on the settings and field type (char), you could have the error 1406: " Data too long for column..." – Chronologist

Answer 4

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;

Foreknowledge

Answer 5

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)

Stefanstefanac