I have this kind of data.
ID
1899
11999
008999
I need to update the data into this.I just need to fill the '0' or '00' in front and remains if sufficient 6 digits.
ID
001899
011999
008999
-
What is the datatype of id?P.Salmon– P.Salmon2020-01-02 10:01:07 +00:00Commented Jan 2, 2020 at 10:01
Add a comment
|
2 Answers
First change Data-type of that column to char
ALTER TABLE `table` CHANGE `id` `id` CHAR(6);
Then use LPAD()
UPDATE table SET `id`=LPAD(`id`, 6, '0');
5 Comments
Joop Eggen
LPAD(TRIM(id), 6, '0'); after CHAR(6) (but indeed VARCHAR is not needed, and INT impossible).
CaffeinatedCod3r
TRIM() will not be required as data is already trimmed when it was stored as INT()Joop Eggen
008999 suggests VARCHAR but that could indeed be a copy error.CaffeinatedCod3r
@JoopEggen Can you post the error message so i can look into it.
Joop Eggen
congrats; I meant an error in typing the question, in the section "I have this kind of data".
I would suggest not doing this update, and not storing your numeric data as text. Instead, if you need to view the numbers with padded zeroes, just handle that when you query, e.g.
SELECT RIGHT(CONCAT('000000', ID), 6) AS ID_padded
FROM yourTable;
Strictly speaking we should also cast ID to text before calling CONCAT, but MySQL should be able to cope with this via implicit conversion.
