0

I'm dealing with the nasty subject of Addresses in SQL. For the most part my data is good.

For the problem records I have a field in SQL called "FULLADDRESS" which I need to split into "Unit", "PrefixLetter", "HouseNumber", "SuffixLetter", "StreetName"

example FULLADDRESS:

FULLADDRESS
345A MIDAVALO WAY
R73 MIDAVALO WAY
10/22 MIDAVALO WAY
3/R85C MIDAVALO WAY

where
3 = UNIT
R = PREFIXLETTER
85 = HOUSENUMBER
C = SUFFIXLETTER

There also may be some with numbers following the road - these are part of the road name e.g.

FULLADDRESS
R3050 HIGHWAY 29

What is a way I can separate the different house number parts in a query? Am i going to be able to do this SQL or do I need to export into Excel and manipulate there (this is not preferred but can be done if it's the only option).

The addresses always have the same structure, however not all of them have all components.

I've started with 

SELECT
    substring(FULLADDRESS, 0, charindex('/', FULLADDRESS, 0)) as UNIT
FROM
    ADDRESS

but I'm now unsure how to continue to pull out all parts of the address particularly where I need some after the / and some where there is no / etc.

Midavalo
  • 469
  • 2
  • 18
  • 29
  • 4
    It is possible in SQL, but I'd not do it in SQL - will take a lot of char/patindex work which could be done in user defined functions or using CLR classes. But I'd make an application, pull the data into the code layer and do the manipulation there allowing you much greater toolbox. SQL is a relational database, other tools are better for string manipulations. – Allan S. Hansen Mar 20 '16 at 19:45
  • 1
    I would leave your information alone, as is and in one string. Other Runtime Tools can split the data based on official US Postal or other postal data systems. You will never get it right, you are creating a step that you will find is unnecessary. The Address as you have it is more right than you can ever make it via parsing algorithms. – Sql Surfer Mar 20 '16 at 23:31
  • 1
    Do this through SSIS, where you have access to C# and regex. – JLB Mar 21 '16 at 00:09
  • @SqlSurfer unfortunately we already have the two split and in these problem they are not in sync (the split addresses don't match the full address) so I'm trying to split them out to correct the incorrect split values. – Midavalo Mar 21 '16 at 07:10
  • 1
    Are you open to using a street address API? They can do all the hard work of splitting the address into components for you – camiblanch Mar 21 '16 at 16:26
  • @camiblanch Yes and no. While I'm open to trying anything, I think this is starting to head way out of my realm of understanding. I had guessed (incorrectly it seems) that this could be handled fairly easily with SQL, but I think now I'll change tack and attempt to do this with some Python. – Midavalo Mar 21 '16 at 18:58

0 Answers0