MySQL split cell value-CodePudding

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;

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring says:

A value of 0 for pos returns an empty string.

In other words, the substring position is 1-based, not 0-based.

CodePudding user response：

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