-1

I have an address string that I am attempting to split into 3 different columns: houseNumber, streetName, streetTag. The origin field, address, has an unknown amount of characters in it, as houseNumber could be 2 digits or 5 digits.

Here is the source data:

rowNum: 2
address: 6008 Spruce Rd

Here is what I tried:

update [originDB].dbo.[originTable]
set houseNumber = (select substring(address, 1, CHARINDEX(' ', address) -1)    
AS houseNumber
from [originDB].dbo.[originTable] where rowNum = 2)
--select * from [originDB].dbo.[originTable]
where rowNum = 2

It corrected split the houseNumber, but I'm not quite sure how to then split at the space again to get streetName and streetTag. I cannot write my own function (policy restrictions.)

My original idea was to split from one direction (as the streetTag will always be the last word in the string), split from the other (for the houseNumber), and then use whatever is left over as the streetName, with the understanding there may be some manual cleanup.

Any suggestions? Thank you!

o11c
  • 13,564
  • 4
  • 46
  • 66
Michelle
  • 335
  • 3
  • 16
  • What database are you using? – Tim Biegeleisen Jun 30 '15 at 02:02
  • 1
    You are going to run into big problems here. How will you deal with addresses having street names which contain 3 or even 4 or 5 strings? – Tim Biegeleisen Jun 30 '15 at 02:04
  • I'm using SQL Server. My original idea was to split from one direction (as the streetTag will always be the last word in the string), split from the other (for the houseNumber), and then use whatever is left over as the streetName. Think that's possible? – Michelle Jun 30 '15 at 02:09
  • This sounds feasible with the caveat that it might not work for every record. Edit your question and I will upvote you. – Tim Biegeleisen Jun 30 '15 at 02:13
  • You are looking for something like this to find the last occurrence of a space in the address field. REVERSE(SUBSTRING(REVERSE(address),0,CHARINDEX(' ',REVERSE(address)))) – Patrick Murphy Jun 30 '15 at 02:13

1 Answers1

0

How it works:

Using the code found here:

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[char]',REVERSE([field]))))

We can find the last occurrence of [char] in [field]. (space in address)

6008 New Mexico St

    ^1st Space  ^Last space
  • Take the house number just as you have. 6008
  • Then take the substring from the first space to the last, aka the street name. New Mexico
  • Leaving everything after the last space as the street tag. St

Query Parts

This one takes the substring of address from the position of the length of house number, to the last occurrence of a space streetName = (select substring(address, len(houseNumber)+1, REVERSE(SUBSTRING(REVERSE(address),0,CHARINDEX(' ',REVERSE(address)))))

This one takes the substring of address from the last occurrence of a space to the length of address (the end of address). streetTag = (select substring(address, REVERSE(SUBSTRING(REVERSE(address),0,CHARINDEX(' ',REVERSE(address)))), len(address))

Full Query

update [originDB].dbo.[originTable]
set houseNumber = (select substring(address, 1, CHARINDEX(' ', address) -1)    
AS houseNumber, 
streetName = (select substring(address, len(houseNumber)+1, REVERSE(SUBSTRING(REVERSE(address),0,CHARINDEX(' ',REVERSE(address))))) AS streetName,
streetTag = (select substring(address, REVERSE(SUBSTRING(REVERSE(address),0,CHARINDEX(' ',REVERSE(address)))), len(address)) AS streetTag
from [originDB].dbo.[originTable] where rowNum = 2)
--select * from [originDB].dbo.[originTable]
where rowNum = 2
Community
  • 1
  • 1
Patrick Murphy
  • 2,268
  • 11
  • 17