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

@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