How to update multiple rows with different values ​​in MySQL?

Question

I have a table with column "A". Some rows have 14 digits for column "A" and some have only 12. I need to convert all entries to 14 digits. Data Type - varchar

I would like to update all the lines at once (one query) by adding zeros before the first digit, so an entry like 012345678910 will become 00012345678910.

Is it possible to do this in one query? Thanks

Answer 1

This should do what you want:

UPDATE your_table SET column_name = LPAD(column_name, 14, "0")
WHERE LENGTH(column_name) < 14

Answer 2

just refresh all lines whose length is 12 and add “00”

UPDATE `table`
SET `col` = '00'+`col`
WHERE LENGTH(`col`) = 12

Answer 3

update table1 set columnA=concat('00',columnA) where char_length(columnA)=12