Mysql – Remove substring from one column and put it on other column

MySQLstringsubstringupdate

I have a table like this:

CREATE TABLE `mytable` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `column1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `column2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And some data:

id | column1                                       | column2
____________________________________________________________
1  | Something                                     |
2  | Something else - Lets add more data           |
3  | Something else - Another example - More stuff |

I would like to update this table, by putting everything after the first - (space + dash + space) into column2 and then removing it from column 1. If there is no -, leave it as it is. Such as this:

id | column1        | column2
__________________________________________________
1  | Something      |
2  | Something else | Lets add more data
3  | Something else | Another example - More stuff

Any suggestions?

Best Answer

UPDATE mytable
SET column2 = SUBSTRING(column1, 3 + LENGTH(SUBSTRING_INDEX(column1, ' - ', 1))),
    column1 = SUBSTRING_INDEX(column1, ' - ', 1)
Related Question