By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table my_table (
brand varchar(20),
model varchar(15) );
insert into my_table (brand) values
('Audi R8'),
('Audi R8'),
('Audi Rs2'),
('Audi Rs4'),
('Audi Rsq8 ');
Records: 5 Duplicates: 0 Warnings: 0
select * from my_table;
| brand | model |
|---|---|
| Audi R8 | null |
| Audi R8 | null |
| Audi Rs2 | null |
| Audi Rs4 | null |
| Audi Rsq8 | null |
select brand,
SUBSTRING_INDEX(brand,' ',-1) as model ,
SUBSTRING_INDEX(brand,' ',+1) as new_brand
from my_table ;
| brand | model | new_brand |
|---|---|---|
| Audi R8 | R8 | Audi |
| Audi R8 | R8 | Audi |
| Audi Rs2 | Rs2 | Audi |
| Audi Rs4 | Rs4 | Audi |
| Audi Rsq8 | Audi |
update my_table
set model = SUBSTRING_INDEX(brand,' ',-1) ,
brand= SUBSTRING_INDEX(brand,' ',+1)
where LENGTH(brand)-LENGTH(REPLACE(brand, ' ', '')) =1
Rows matched: 4 Changed: 4 Warnings: 0
select * from my_table;
| brand | model |
|---|---|
| Audi | R8 |
| Audi | R8 |
| Audi | Rs2 |
| Audi | Rs4 |
| Audi Rsq8 | null |