Split part function in postgreSQL

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,

>Solution :

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

Leave a Reply