Question 90797 in PostgreSQL Question posted in
The official documentation can be found here.

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,
Tags:

2

Answers


  1. 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
    Login or Signup to reply.
  2. 0
    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.

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search