Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading