3

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?

1
UPDATE mytable
SET column2 = SUBSTRING(column1, 3 + LENGTH(SUBSTRING_INDEX(column1, ' - ', 1))),
    column1 = SUBSTRING_INDEX(column1, ' - ', 1)
0
SELECT column1, LEFT(column1, Position(' - ' in column1)) as newCol1,  
substring(column1, Locate(" - ",column1)+3) as newCol2 
FROM mytable where column1 like '% - %';

This should get you in the right direction. If it gives you your desired results you can use it to create your Update statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.