How do I split the zipcode and state in this table? I had no trouble separating the street and city, but I struggled with the ZIP code and State part

944 Walnut Street, Boston, MA 02215 This should be the output:

|       Street     | City | State | ZipCode
:------------------:------:-------:-------:
| 944 Walnut Street|Boston|  MA   | 02215

I tried doing this but this is the result

SELECT
    split_part(purchaseaddress::TEXT, ',', 1) Street,
    split_part(purchaseaddress::TEXT, ',', 2) City,
        split_part(purchaseaddress::TEXT, ',', 3) State,
        split_part(purchaseaddress::TEXT, ' ' , 4)ZIPCode
FROM
    sales_2019;

|       Street     | City |    State   | ZipCode
:------------------:------:------------:-------:
| 944 Walnut Street|Boston|  MA 02215  | Boston,

CodePudding user response：

1. You can use string_to_array() split it once, then pick the fields by index.
2. Make sure you trim() the elements before trying to split by space to avoid empty leading and trailing elements.
3. You need to split twice: once the whole thing by commas, then the 3rd element by space.

Online demo.

with 
 sales_2019(purchaseaddress) as 
  (values ('944 Walnut Street, Boston, MA 02215') )
,address_split_by_commas as 
  (select string_to_array(purchaseaddress::TEXT, ',') arr from sales_2019 )
SELECT
    trim(arr[1]) Street,
    trim(arr[2]) City,
    split_part(trim(arr[3])::TEXT, ' ' , 1) State,
    split_part(trim(arr[3])::TEXT, ' ' , 2) ZIPCode
FROM
    address_split_by_commas;
    

--      street       |  city  | state | zipcode
--------------------- -------- ------- ---------
-- 944 Walnut Street | Boston | MA    | 02215
--(1 row)

CodePudding user response：

Try this:-

SELECT
trim(split_part(purchaseaddress::TEXT, ',', 1)) Street,
trim(split_part(purchaseaddress::TEXT, ',', 2)) City,
trim(split_part(trim(split_part(purchaseaddress::TEXT, ',', 3))::TEXT, ' ', 1)) State,
trim(split_part(trim(split_part(purchaseaddress::TEXT, ',' , 3))::TEXT, ' ', 2)) ZIPCode
FROM
sales_2019;

output:-

        street       |  city  | state | zipcode
   ------------------ -------- ------- ---------
   944 Walnut Street | Boston |   MA  | 02215