splitting strings in sql

Question

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

Does this answer your question? Substring after a space

Nino van der Mark
– Nino van der Mark

2021-06-09 06:59:37 +00:00
Commented Jun 9, 2021 at 6:59 — Nino van der Mark
– Nino van der Mark, Commented Jun 9, 2021 at 6:59
Do you have more than one andin a row?

sddk
– sddk

2021-06-09 07:02:31 +00:00
Commented Jun 9, 2021 at 7:02 — sddk
– sddk, Commented Jun 9, 2021 at 7:02
Refer : stackoverflow.com/questions/6233037/…

Gehan Fernando
– Gehan Fernando

2021-06-09 07:06:16 +00:00
Commented Jun 9, 2021 at 7:06 — Gehan Fernando
– Gehan Fernando, Commented Jun 9, 2021 at 7:06
@sddk I just have one

jean10
– jean10

2021-06-09 07:12:56 +00:00
Commented Jun 9, 2021 at 7:12 — jean10
– jean10, Commented Jun 9, 2021 at 7:12
@GehanFernando Not applicable, check DBMS tag.

Akina
– Akina

2021-06-09 07:21:52 +00:00
Commented Jun 9, 2021 at 7:21 — Akina
– Akina, Commented Jun 9, 2021 at 7:21

Akina · Accepted Answer · 2021-06-09 07:24:01Z

3

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

answered Jun 9, 2021 at 7:24

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

axnet · Accepted Answer · 2021-06-09 07:41:07Z

0

    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
        ;

answered Jun 9, 2021 at 7:41

axnet

5,8403 gold badges30 silver badges49 bronze badges

Collectives™ on Stack Overflow

splitting strings in sql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related