SQLTeam.com | Weblogs | Forums

Extract Addressline,State,City,Zipcode into different columns from one single column called "Adress" with space as delimeter using Posgresql

Transact-SQL
#1

Hi All,

Problem statment is as described in the headline
Using postgresql

Example value in "Address"column
4 drive rd phoneix Arizona 34445

Needs to be extracted into differaent columns as Addressline,State,City,Zipcode

Biggest challenge is the space delimeter as all rows are not identical for ex
Row1:4 drive rd phoneix Arizona 34445
Row2 :Alm dr missouri 65034

Looking forward to hearing back

Thanks!

#2

Welcome

You might want to leverage tools specifically designed for parsing and cleaning data. Trying to do this via sql would be very difficult especially if there is no consistency. Also this is a microsoft sql server forum though some might help

1 Like
#4

image

#5

Thank you for the response,Could you please state an exame query with the required function

Thanks,
Shreyas

#6

image
image583×532 14.9 KB

#7

Thank you harish.

I have a work around which I am looking into.I have a table that has derived city,State and Zip columns from the address column however it is based on event column called "in" and "out" for ids

So for one 1 id there will be 2 rows having all columns based on the event column value in and out

Example.
Id. "Address" city. State. Zip. Event

  1. 4 drive rd phoix Arizona 34445. Phoneix. Ar. 34445. IN

  2. 5 drive rd almx Missiippi 34445. Phoneix. Ar. 34445. Out

Ids are same so have to pick the address based on eventnames