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:
I then did write the following query to extract the street, house number and addition.
When the query is executed this results in the following
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.