I have a table ML, with column Klubs:
I want to split this column so I have two columns like this. New table name ML2 and column name Region.
First column can have 2 words if they are different from 2nd column word.
I am new to MySQL and searched all over stackoverflow and internet , but no luck.
How can split column Klubs from table ML as in 1st picture into new table ML2 with 2 columns as in 2nd picture?
Assuming that your second column does not include any spaces, we can search for the location of the last space, and then split there:
SELECT Klubs, substr(Klubs, 1, char_length(Klubs) - instr(reverse(Klubs),' ')) as Region, substr(Klubs, char_length(Klubs) - instr(reverse(Klubs),' ')+2) as Club FROM ML;
You could insert it in a new table (ML2):
create table ML2 <the query>
or create a view:
create view ML2 as <the query>