I have a table called CUSTTABLE on an sql server database that has a column containing addresses. In the Netherlands the street name always comes before the house number and then the apartment information if it's an apartment. I want to separate the street name, the number information into two strings. The data looks like this:
CUSTTABLE
Kerkstraat 1
Kleine Kerkstraat 5B
Kerstraat 118B/A5
Thus, some street names have more than 1 word, and some adresses have apartment information and some don't. Some house numbers have non-numeric characters as well. I want it to be:
Street_name House_number
Kerksstraat 1
Kleine Kerkstraat 5B
Kerkstraat 118B/A5
I'm able to extra the Street name and House number using the following code. I'm receiving an error when there is a 'NULL' entry in the database when I'm using the following code.
LEFT(STREET,PATINDEX('% [0-9]%',STREET)-1)'Street name',
SUBSTRING(STREET,PATINDEX('% [0-9]%',STREET)+1,PATINDEX('%, %',STREET+ ', ')-PATINDEX('% [0-9]%',STREET)-1)'House number',
