Advertisements
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