s19k15 s19k15 - 1 month ago 7
MySQL Question

MySQL update and split column with mulivalues seperated by comma

I have a database and one of the tables has one column that has some special data but theese data does not meet 1NF,2NF,3NF etc of database structures.

So in that column, i have values like "0,3,1,5,20,40," as varchar, and
i want to write an sql query to update this column and make it like "00,03,01,05,20,40,", also must be noticed that many values have two digits, theese values i don't want to add a zero at front!

COLUMN1
0,3,1,5,20,40,

MUST LOOKS LIKE
00,03,01,05,20,40,


I have tried to do it with replace function.


  • The problem (1), is that i want to keep the previous value when i add the additional zero.

  • The problem (2) is that must be done when values between commas are only one digit!



Then when i fix this problem with sql query i will split this column in an nother table that data follow the database structures.

ID VALUE
1 00
1 03
1 01
1 05
1 20
1 40


So, how can i do that?

Answer

If the elegant approach in the other answer doesn't work, try the brute force approach.

It looks for single digits with a comma each side and replaces it with the the version with a leading zero, and lastly looks for a single digit at the front (by looking for a comma in the second position) and pre-pending a zero.

update test 
SET column1 = REPLACE(column1,",0,",",00,");
update test 
SET column1 = REPLACE(column1,",1,",",01,");
update test 
SET column1 = REPLACE(column1,",2,",",02,");
update test 
SET column1 = REPLACE(column1,",3,",",03,");
update test 
SET column1 = REPLACE(column1,",4,",",04,");
update test 
SET column1 = REPLACE(column1,",5,",",05,");
update test 
SET column1 = REPLACE(column1,",6,",",06,");
update test 
SET column1 = REPLACE(column1,",7,",",07,");
update test 
SET column1 = REPLACE(column1,",8,",",08,");
update test 
SET column1 = REPLACE(column1,",9,",",09,");
update test 

SET column1 = concat("0",column1)
where mid(column1,2,1) = ",";

DEMO