Mārcis Liepiņš Mārcis Liepiņš - 1 year ago 75
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?

Answer Source

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;

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>