Splitting data into two columns

  • Senchi

    Ten Centuries

    Points: 1074

    In address_field I have street name together with house number all in one line.
    Something like : One Street 1A
    I need this separated into two columns.
    I want the house number to go into the house_number column
    I need a query that will catch the first number in the address field and all data after it and move it
    to the house_number column.
    So I have :

    Address         House_Number
    One Street        1A

    Here the table :
    USE [tempdb]
    GO
    CREATE TABLE [dbo].[TEMP]
    (
        [adress]   [nvarchar](50),
        [street_number] [nvarchar](5)
    )
    go
    INSERT INTO TEMP (adress)
    values

    ('One Street 1A'),
    ('Two Street 2AB'),
    ('Three Street 3BA')

    How can I do this ?
    Ohh,and I forgot to ask also: Is it possible to split the street number into numbers and letters so they are not together?
    Like if it's 1A then it becomes 1 A.

  • Thom A

    SSC Guru

    Points: 98219

    This splits out the two parts, but not the letters and numbers:
    CREATE TABLE #Sample ([address] nvarchar(50), --Address has 2 Ds, not 1
                 street_number nvarchar(5));
    GO

    INSERT INTO #Sample ([address])
    VALUES
    ('One Street 1A'),
    ('Two Street 2AB'),
    ('Three Street 3BA');
    GO

    SELECT *
    FROM #Sample;

    WITH AddressSplit AS (
      SELECT [address],
            RIGHT([address], CHARINDEX( ' ', REVERSE(Address)) - 1) AS Number
       FROM #Sample)

    SELECT LEFT([address], LEN([address]) - len(Number) - 1) AS Street,
           Number
    FROM AddressSplit;

    GO
    DROP TABLE #Sample;
    GO

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Senchi

    Ten Centuries

    Points: 1074

    Wow, Thom....wonderful ..
    What if I have the case when numbers and letters are separated like : 1 A   ?
    Right now, when I tested  , soon as I add space it does not work. 1A inserts OK, 1 A does not.
    Only 'A' goes into 'number'.

  • J Livingston SQL

    SSC Guru

    Points: 51272

    Senchi - Saturday, February 25, 2017 7:06 AM

    Wow, Thom....wonderful ..
    What if I have the case when numbers and letters are separated like : 1 A   ?
    Right now, when I tested  , soon as I add space it does not work. 1A inserts OK, 1 A does not.
    Only 'A' goes into 'number'.

    probably best for everyone if you provide all the possible variants you may have...dont you think?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Senchi

    Ten Centuries

    Points: 1074

    J Livingston SQL - Saturday, February 25, 2017 7:47 AM

    Senchi - Saturday, February 25, 2017 7:06 AM

    Wow, Thom....wonderful ..
    What if I have the case when numbers and letters are separated like : 1 A   ?
    Right now, when I tested  , soon as I add space it does not work. 1A inserts OK, 1 A does not.
    Only 'A' goes into 'number'.

    probably best for everyone if you provide all the possible variants you may have...dont you think?

    well, usually its 20B  or 20 B . I cant think of anything else sane.:)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply