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!