SQL: Split string by delimiter multiple times into new columns

Question

I have ids and corresponding strings like this:

id    place
234   USA_NY_Buffalo

I want to split up the place string into three strings each with its own column:

id    Country  State  City
234   USA      NY     Buffalo

When I try splitting the string like this:

SELECT id, SPLIT(place,"_") FROM mytable

I get

id    place
234   USA
234   NY
234   Buffalo

Is it possible to split the string by the first "_", then split the second string again by " _ " ?

Which RDBMS are you using?

Gurwinder Singh
– Gurwinder Singh

2017-04-07 03:54:17 +00:00
Commented Apr 7, 2017 at 3:54 — Gurwinder Singh
– Gurwinder Singh, Commented Apr 7, 2017 at 3:54

Learning · Accepted Answer · 2017-04-07 03:40:07Z

2

If the string would always have Country,State and City only then you could try something like.

select SUBSTRING_INDEX('USA_NY_Buffalo', '_', 1) AS country,
SUBSTRING_INDEX(SUBSTRING_INDEX('USA_NY_Buffalo', '_', 2), '_', -1) AS State,
SUBSTRING_INDEX(SUBSTRING_INDEX('USA_NY_Buffalo', '_', 3), '_', -1) AS City;

answered Apr 7, 2017 at 3:40

Learning

1631 silver badge11 bronze badges

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

Collectives™ on Stack Overflow

SQL: Split string by delimiter multiple times into new columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related