Split part function in postgreSQL

Question

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,

Zegarek · Accepted Answer · 2022-11-24 14:15:02Z

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

Online demo.

create table sales_2019 (purchaseaddress text);
insert into sales_2019 values ('944 Walnut Street, Boston, MA 02215');

with 
 address_split_by_commas         as 
  ( select string_to_array(purchaseaddress::TEXT, ',') arr 
    from   sales_2019 )
,address_split_by_commas_trimmed as 
  ( select array_agg( trim(element) ) arr 
    from ( select unnest(arr) element 
           from   address_split_by_commas) a )
SELECT
    arr[1]                     Street,
    arr[2]                     City,
    split_part(arr[3], ' ', 1) State,
    split_part(arr[3], ' ', 2) ZIPCode
FROM
    address_split_by_commas_trimmed;
    

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

DEEPAK TOMAR · Accepted Answer · 2022-11-24 09:24:14Z

0

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

edited Nov 24, 2022 at 9:24

answered Nov 24, 2022 at 9:12

DEEPAK TOMAR

342 bronze badges

Collectives™ on Stack Overflow

Split part function in postgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related