Mārcis Liepiņš Mārcis Liepiņš - 6 months ago 28x
SQL Question

Splitting column into two columns with different string counts in MySQL

I have a table ML, with column Klubs:

enter image description here

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.

enter image description here

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

I search for the the space with instr, and to do this reverse I use the reverse function on the string first, and then to compensate I subtract it from the length of the string with char_length.

You could insert it in a new table (ML2):

create table ML2
<the query>

or create a view:

create view ML2 as
<the query>