I need help separating addresses (number, addition, etc.)

Question

Apologies for the fuzzy name ...

My problem is this; I have a SQL Server table with about 100,000 records. Each person has an address, something like "Nieuwe Prinsengracht 12 - III." Now the client wants to separate the street from the number and add (so each address becomes two or three fields). The problem is that we cannot be sure of the format in which the current address is located, it may just be something like "Velperweg 30".

The only thing we know about this is that it is a piece of text followed by a number, possibly followed by some other text (which may contain a number).

A possible solution is to do this with regular expressions, but I would very (many, many) do this with a query. Is there a way to use regular expressions in a query? Or do you have other suggestions on how to solve this 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]

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

Answer 2

SQL Server T-SQL - .., # VB.NET, Regex, SQL-CLR T-SQL.

"" T-SQL SUBSTRING CHARINDEX - .

Answer 3

: " ?", , .NET. CLR , . Visual Studio 2008 . SQL- .

Answer 4

, , , , .

, , . - , .

. , , , .

.

Answer 5

" , , , " ". , , , T-SQL ( ). , , .

Answer 6

TGnat . .

.

spring , , . .

, , , 100 000 . , , .

.

Answer 7

, , ( ). Excel, . SublimeText (- ) (CTRL-H, ):

FIND: ^('?\d?\d?\d?['-\.a-zA-Z ]*)(\d*).*$
REPLACE FOR THE HOUSE NUMBER: $2
REPLACE FOR THE STREET NAME:  $1

:

• Some addresses begin with a quote, for example. 't Hofje, so I needed to add'?
• Some addresses contain numbers at the beginning, for example. 17 Septemberplein or 2e Molendwarsstraat, so I added \ d? \ D? \ D?
• Some addresses include, for example, Willem-Aleksanlan or "