MySQL split cell value

user_1172739 0
Viewed 30

I have the following MySQL code, which should (for each row) take the value of the column Cislo and then update the current row, setting the column Cislo and Nazov to SUBSTRINGs of the current value.

So let's say currently Cislo = 12345LoremIpsum and Nazov='', after the update it should be Cislo=12345 and Nazov=LoremIpsum.

I have tried many different approaches, however the results are always empty cells.

UPDATE `skladovekartyvyrobok00001` SET 
    `Cislo`= SUBSTRING(`Cislo`,0,5),
    `Nazov`= SUBSTRING(`Cislo`,5)
    WHERE `ID`=31;
mysql sql
2 Answers
Bill_Karwin_208600

The second parameter of SUBSTRING might start as 1 instead of 0

A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string.

Then you can use a little trick to update Nazov column first and then update Cislo

I think you might try this.

UPDATE `skladovekartyvyrobok00001` SET 
    `Nazov`= SUBSTRING(`Cislo`,6),
    `Cislo`= SUBSTRING(`Cislo`,1,5)
    WHERE `ID`=31;

sqlifddle

user_51760710
Related