Help with splitting an address

Question

Post reply

Help with splitting an address

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
August 5, 2016 at 1:39 am

#315954

I have a field called Address1 in my table Table1.

Here is an example of data in that field -

8 Brick Lane and 11 Balkerne Drive or it could be 1c Safe Street

I am writing a query to try and achieve the following from the Address1 field.

In the examples above 8 and 11 would pull back as "HouseNumber".

In the examples above 1c would pull back as "HouseName"

In the examples above Safe Street, Brick Lane and Balkerne Drive would appear in "Street".

Any help would be greatful.

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

TSQL Tryer (8/5/2016)

I have a field called Address1 in my table Table1.

Here is an example of data in that field -

8 Brick Lane and 11 Balkerne Drive or it could be 1c Safe Street

I am writing a query to try and achieve the following from the Address1 field.

In the examples above 8 and 11 would pull back as "HouseNumber".

In the examples above 1c would pull back as "HouseName"

In the examples above Safe Street, Brick Lane and Balkerne Drive would appear in "Street".

Any help would be greatful.

Thanks

Addresses cannot be resolved out in this way with any useful degree of precision, by using code alone. There are a few address correction packages out there which do this by comparing to a reference database, and even they don't achieve 100%. Try QuickAddress if you're in the UK.

Edit: quote messed up.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Spiff SSCertifiable Points: 7766 More actions · Answer 2

SQL is never really ideal for this sort of thing, using SSIS would be preferable, however when I want to check if something is a valid number I use TRY_CONVERT which returns NULL if it cannot parse the text into a number.

An example would look like:



WITH addresses AS (

	SELECT LEFT(address,CHARINDEX(' ',address))  AS house

	FROM (VALUES

	('8 Brick Lane'),

	('11 Balkerne Drive'),

	('1c Safe Street')) AS t(address)

)

SELECT

	TRY_CONVERT(int, house) AS [Housenumber],

	CASE WHEN TRY_CONVERT(int, house) IS NULL THEN RTRIM(house) ELSE NULL END AS [Housename]

FROM addresses

There is also TRY_PARSE().

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

Thanks - Ended up trying the following and it did the trick -

CASE WHEN PATINDEX('%[^0-9]%',left(Address1,CHARINDEX(' ',Address1,0)-1)) = 0



		THEN left(Address1,CHARINDEX(' ',Address1,0)-1) ELSE NULL END AS BuildingNumber,

		CASE WHEN PATINDEX('%[^0-9]%',left(Address1,CHARINDEX(' ',Address1,0)-1)) > 0

		THEN left(Address1,CHARINDEX(' ',Address1,0)-1) ELSE NULL END AS BuildingName,

Right(Address1,Len(Address1)-CHARINDEX(' ',Address1,0)) AS Street,