Extract addressline, state, city, zipcode into different columns from one single column called “adress” with space as delimeter using posgresql

Question

1.00/5 (1 vote)

See more:

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!

What I have tried:

Tries splitpart function however since the data is inconsistent in different rows having issues getting accurate values

Posted 8-May-22 19:44pm

Member 14019423

Updated 10-May-22 11:57am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2022-05-08T20:23:00

Don't. You can't really automate that successfully without having a lookup table for each possible address in the country.
For example, where do you break "peach pine road lake havasu city arizona" that gives you three segments "peach pine road", "lake havasu city", "arizona" that doesn't cause problems with "three bears lake", "north little rock", "arkansas"?

The only way to break that info up correctly would be to use a lookup on the zipcode and then try to find street / house / apartment info from the results of that together with the original input.

And doing that in SQL? I wouldn't go there ... use a presentation language instead.

And if you are going to invest time in a presentation language instead, then why not do it more sensibly, and separate teh address info at source? instead of getting addresses entered as one line of text, get it broken up into apartment, street, town, county, state, zipcode at the point of data entry and store it separately. M

Maciej Los · Answer 2 · 2022-05-10T03:42:00

There's a chance to split address data into parts using regex[^].

Assuming that, last 3 pieces of address column are:
- postal abbrevation: 2 letters,
- city: one or more words,
- zipcode: 5 digits
you'll be able to split address into parts.

This regex pattern should do the job: ^(.*?)\s([a-z]{2})\s(.*?)\s(\d{5})$

See: PostgreSQL REGEXP_MATCHES: Extracting Text Based on a Regex[^]

[EDIT]

Example:

SQL

WITH MyData AS
(
  SELECT '4 drive rd phoneix Arizona 34445' AS address
  UNION ALL
  SELECT 'Alm dr missouri 65034'  AS address
)
SELECT parts[1] AS addressline, parts[2] AS state, parts[3] AS city, parts[4] AS zipcode 
FROM (SELECT regexp_matches(address, 
  '^(.*?)\s([a-z]{2})\s(.*?)\s(\d{5})$') parts FROM MyData) t;

Result:

addressline 	state 	city 	 	 	 	zipcode
4 drive 	 	rd 	 	phoneix Arizona 	34445
Alm 	 	 	dr 	 	missouri 	 	 	65034

Online demo: Postgres 10 | db<>fiddle[^]

Patrice T · Answer 3 · 2022-05-10T11:57:00

Quote:
Biggest challenge is the space delimeter as all rows are not identical for ex

It is not a challenge as space have multiple usage in your addresses, it is a bad idea, it is impossible.
You have devised this RegEx

^(.*?)\s([a-z]{2})\s(.*?)\s(\d{5})$

but think abou_t addresses like

4 drive rd phoneix Arizona 34445
Alm dr missouri 65034
4 drive rd phoneix New Mexico 34445
Alm dr New York New York 65034  // aka NY city in NY state
4 drive rd phoneix North Carolina 34445
4 drive rd phoneix South Dakota 34445
4 drive rd phoneix Washington District of Columbia 34445
4 drive rd Baton Rouge Louisiana 34445

You need a way to check every spelling of states and cities with multiple words naming (including wrong spellings).
The only way is to have a separator that can not be part of the address
like:

4 drive rd;phoneix;Washington District of Columbia;34445
4 drive rd;Baton Rouge;Louisiana;34445

As programmer, your job is to device solution that will support all cases. And adding cases not handled by first fast solution takes much more time than doing things right in first place.

Extract addressline, state, city, zipcode into different columns from one single column called “adress” with space as delimeter using posgresql

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0