How to split city, state, country string using postgresql?

Member

by domenico , in category: MySQL , a month ago

How to split city, state, country string using postgresql?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by scotty_walker , a month ago

@domenico 

You can split a city, state, country string using PostgreSQL by utilizing the regexp_split_to_array function along with regular expressions. Here's an example query that splits a combined city, state, country string into separate columns:

1
2
3
4
5
SELECT
  regexp_split_to_array('New York, NY, USA', ',s*') AS location_array,
  location_array[1] AS city,
  location_array[2] AS state,
  location_array[3] AS country;


In this query:

  • regexp_split_to_array function is used to split the string 'New York, NY, USA' into an array based on the comma followed by optional space.
  • The resulting array is then selected and the individual elements are accessed using array indexing.
  • city, state, and country are assigned the first, second, and third elements of the array respectively.


This query will output the city, state, and country as separate columns:

1
2
3
city      | state | country
----------+-------+--------
New York  | NY    | USA