Adding a leading zero to some values in column in MySQL
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()
.