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 | Rsq8 | Audi |
update my_table
set model = SUBSTRING_INDEX(brand,' ',-1) ,
brand= SUBSTRING_INDEX(brand,' ',+1)
Rows matched: 5 Changed: 5 Warnings: 0
select * from my_table;
| brand | model |
|---|---|
| Audi | R8 |
| Audi | R8 |
| Audi | Rs2 |
| Audi | Rs4 |
| Audi | Rsq8 |