By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table tbl_name(id VARCHAR(50), QRscan VARCHAR(50), number VARCHAR(50), name VARCHAR(50), date_ date, name2 VARCHAR(50));
Insert Into tbl_name Values('1.','a12345,test,01/01/2021,tes2','','',null,'');
select substring_index(QRscan, ',', 1) number,
substring_index(substring_index(QRscan, ',', 2), ',', -1) name,
substring_index(substring_index(QRscan, ',', 3), ',', -1) date_,
substring_index(QRscan, ',', -1) name2
from tbl_name
| number | name | date_ | name2 |
|---|---|---|---|
| a12345 | test | 01/01/2021 | tes2 |
update tbl_name
set number = substring_index(QRscan, ',', 1),
name = substring_index(substring_index(QRscan, ',', 2), ',', -1),
date_ = str_to_date(substring_index(substring_index(QRscan, ',', 3), ',', -1), '%d/%m/%Y'),
name2 = substring_index(QRscan, ',', -1)
-- where some condition, i.e. id = 1, number is null... etc
Rows matched: 1 Changed: 1 Warnings: 0
select * from tbl_name
| id | QRscan | number | name | date_ | name2 |
|---|---|---|---|---|---|
| 1. | a12345,test,01/01/2021,tes2 | a12345 | test | 2021-01-01 | tes2 |