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
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;
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
.
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:
Source: Stackoverflow.com