none
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 haveTongue Tiedelect
    substring(Address,0,charindex('',Address)) as number
    ,substring(Address, (charindex('',Address)+1)
    ,len(Address)) as address
    from contact

     

     

    Wednesday, November 14, 2007 3:45 PM

Answers

  • 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))

     

     

    Wednesday, November 14, 2007 3:50 PM
  • 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
    */

     

     


    Wednesday, November 14, 2007 3:55 PM

All replies

  • 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))

     

     

    Wednesday, November 14, 2007 3:50 PM
  • 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
    */

     

     


    Wednesday, November 14, 2007 3:55 PM
  • That works but I have around 6,000 addresses that I am working with. 

     

    Nick

    Wednesday, November 14, 2007 4:12 PM
  • These solutions should work just fine with 6,000 rows.


    Are you encountering a problem?

     

    Wednesday, November 14, 2007 4:24 PM
  • I got it.

     

    Thanks everyone.

     

    Wednesday, November 14, 2007 4:43 PM