[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 877
  • Last Modified:

T-SQL parse street address

Need to pull street number and street name separately from an address.  It should be noted that some of these street names may have more than one word in them, i.e.: 123 Oak Lawn Dr.

Thanks!
0
Rickzzz
Asked:
Rickzzz
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
please post sample data desired output
0
 
RickzzzAuthor Commented:
From
address='123 Oak Lawn Dr'

To:
addressNumber='123'
addressName='Oak Lawn Dr.'

Thanks!
0
 
jaan33Commented:
--assumes the first set of non-space characters are the house numbers

--variable/function
declare @addr varchar(max)
        ,@firstspace int

set @addr = '123 Oak Lawn Dr'
set @firstspace = charindex(' ',@addr)

select substring(@addr,0,@firstspace)          as addrnum
      ,right(@addr, len(@addr) - @firstspace)  as addrstreet




--table results
select substring(addr,0,charindex(' ',addr))          as addrnum
      ,right(addr, len(addr) - charindex(' ',addr))   as addrstreet
from (select '123 Oak Lawn Dr' as addr) tbl
0
 
RickzzzAuthor Commented:
perfect, thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now