August 5, 2016 at 1:39 am
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
August 5, 2016 at 1:58 am
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.
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
August 5, 2016 at 3:00 am
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().
August 5, 2016 at 3:31 am
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,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply