I need help splitting addresses (number, addition, etc)

Question

Apologies for the fuzzy title...

My problem is this; I have a SQL Server table persons with about 100.000 records. Every person has an address, something like "Nieuwe Prinsengracht 12 - III". The customer now wants to separate the street from the number and addition (so each address becomes two or three fields). The problem is that we can not be sure of the format the current address is in, it could also simply be something like "Velperweg 30".

The only thing we do know about it is that it's a piece of text, followed by a number, possibly followed by some more text (which can contain a number).

A possible solution would be to do this with regexes, but I would much (much, much) rather do this using a query. Is there any way to use regexes in a query? Or do you have any other suggestions how to solve such a problem?

Answer 1

Something like this maybe?

SELECT
   substring([address_field], 1, patindex('%[1-9]%', [address_field])-1) as [STREET],
   substring([address_field], patindex('%[1-9]%', [address_field]), len([address_field])) as [NUMBER_ADDITON]
FROM
   [table]

It relies on the assumption that the [street] field will not contain any numbers, and the [number_addition] field will begin with a number.