CODEWITHSUNDEEP
mysqlsqlselectsql-update
Computer User
Computer User

Reputation: 2869

Split column in 2 clomuns using comma as seperator

I have table listings:

location (Primary Key)

Chicago, USA
New York, USA
Paris, France

The format is strictly

City, Country

Same as:

<City><comma><single_space><Country>

I want 2 columns from location (using commas as a separator) as:

city      country

Chicago   USA
New York  USA
Paris     France

Upvotes: 1

Views: 29

Answers (1)

GMB
GMB

Reputation: 222432

In MySQL, you can use substring_index() for this:

select 
    location,
    substring_index(location, ', ', 1) city, 
    substring_index(location, ', ', -1) country
from listings

Demo on DB Fiddle:

location      | city     | country
:------------ | :------- | :------
Chicago, USA  | Chicago  | USA    
New York, USA | New York | USA    
Paris, France | Paris    | France

If you wanted an update statement:

update listings
set 
    city = substring_index(location, ', ', 1), 
    country = substring_index(location, ', ', -1)

Upvotes: 2

Related Questions