Split Address value into two columns

Question

I am trying to get an address field into 2 colums.  I need the number value in one column and street name in another column.

 

The data is stored:

876 blue ct

9987 red dr

23 windyknoll

 

This is what I haveelect
substring(Address,0,charindex('',Address)) as number
,substring(Address, (charindex('',Address)+1)
,len(Address)) as address
from contact

 

 

Answer 1

Try the code below.

 

Chris

 

Code Block

DECLARE @Address varchar(20)

SET @Address = '876 blue ct'

SELECT LEFT(@Address, CHARINDEX(' ', @Address) - 1),

       RIGHT(@Address, LEN(@Address) - CHARINDEX(' ', @Address))

 

 

Answer 2

I saw the same thing:

 

Code Block

declare @contact table (address varchar(20))
insert into @contact
select '876 blue ct' union all
select '9987 red dr' union all
select '23 windyknoll'

 

select
substring(Address,0,charindex(' ',Address)) as number
,substring(Address, (charindex(' ',Address)+1)
,len(Address)) as address
from @contact

 

/*
number               address
-------------------- --------------------
876                  blue ct
9987                 red dr
23                   windyknoll
*/

 

 

Answer 3

That works but I have around 6,000 addresses that I am working with. 

 

Nick

Answer 4

These solutions should work just fine with 6,000 rows.

Are you encountering a problem?

 

Answer 5

I got it.

 

Thanks everyone.