donderdag 9 juni 2011

Extract house number & addition from address line


One of my customers needed to deliver a file with street name, house number and house number addition split over the 3 columns. The system that it was needed from did only have one address line that contained the entire street address including the house number and addition.

Addresses are not filled in following a standard like saying street-space-housenumber-space-addition but spaces, dots and dashes where used. I produced the small table example below:

image

I then did write the following query to extract the street, house number and addition.

image

When the query is executed this results in the following

image

The results is what I needed, I only find the query not that nice, when you have a simple solution please let me know.

I used several REPLACE statements to get the dash and dots out of the results. You could use the function created by Christian d'Heureuse to get the special characters out http://www.source-code.biz/snippets/mssql/1.htm

You can download the above code here: blog56

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.

Dynamic Intelligence | LinkedIn | Blog | Twitter

1 opmerking:

Erwin van Ek zei

Beste Arjan,

Ik ben bezig met een migratie traject waarbij ik 1 adres veld moet splitsen naar straat, huisnummer en ext. Ik zag in jou blog een prachtige uitwerking. Helaas alleen een plaatje en is het sql script niet meer te vinden. Heb jij deze nog en zou ik deze mogen? je kan me bereiken op defries@hotmail.com. mvg Erwin van Ek