splitting strings in sql

I have a table with 2 columns, one for id and one for country.

I’ve found some rows in the table where it looks like the below:

ID	Country
1	US and Canada
2	France and UK

How can I write a query so that I find the rows and split the column so I get the following result.

ID	Country
1	US
1	Canada
2	France
2	UK

Answer

Do you have more than one and in a row? – sddk

I just have one – jean10

SELECT id, SUBSTRING_INDEX(Country, ' and ', 1) Country
FROM tablename
UNION 
SELECT id, SUBSTRING_INDEX(Country, ' and ', -1)
FROM tablename
ORDER BY 1