CODEWITHSUNDEEP
mysqlsql
jean10
jean10

Reputation: 171

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

Upvotes: 0

Views: 74

Answers (2)

axnet
axnet

Reputation: 5770

    SELECT id, SUBSTRING_INDEX(name, ' and ', 1) as country
        FROM 
           (      SELECT 1 AS id, 'US and CN' AS name 
            UNION SELECT 2 AS id, 'JP and KR' AS name
           ) as string_test_tbl
        UNION 
    SELECT id, SUBSTRING_INDEX(name, ' and ', -1) as country
        FROM 
           (      SELECT 1 AS id, 'US and CN' AS name 
            UNION SELECT 2 AS id, 'JP and KR' AS name
           ) as string_test_tbl
        ;

Upvotes: 0

Akina
Akina

Reputation: 42612

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

Upvotes: 3

Related Questions