Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Unanswered: How to put address in different columns using carraige returns

    Hi All,

    I'm trying to put an address into different columns from a single column. Right now the data is stored in one column with the data separated by carriage returns.

    I've tried using SUBSTRING function but to no avail.

    All i've got so far is:

    SELECT
    nme AS IntroducerName
    ,addrss AS Address
    FROM inintrdcr

    And the results are populated as :

    IntroducerName Address
    Mickey Mouse Mickey Mouse Street Disney Land Paris

    Whereas i want the address to be separated into different columns by the carriage returns.

    New Column | New Column | New Column | New Column
    Mickey Mouse Street | Disney Land | Paris | Very Expensive!

    Any ideas?

    Your help would be much appreciated

    Krunal

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is there a limit to the number of lines in an address?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    5
    Hi Georgev,

    Nope there isn't it's just basically a text field where each line is separated by a carriage returns

    I can get them list in one column one after another (see code below) but unfortunately not on different columns.

    SQL:
    SELECT
    CAST (nme AS VARCHAR) + CHAR(13) +
    CAST (addrss AS VARCHAR(200)) AS Address
    FROM inintrdcr

    Krunal
    Last edited by Kmakwana; 12-09-08 at 12:47.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Kmakwana
    I'm trying to put an address into different columns from a single column
    By this do you mean select the result or update existing table columns?
    If it's the latter then you need to have a pre-defined number of destination columns, right?
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2008
    Posts
    5
    Quote Originally Posted by georgev
    By this do you mean select the result or update existing table columns?
    If it's the latter then you need to have a pre-defined number of destination columns, right?
    Hi Georgev,

    basically just select the result into different columns. I want to extract the data from an existing column e.g Address which has all the details but separate the details into difference columns

    Currently:
    Address: James Bond, 007 Building, Bond Street, London

    Ideally:
    column 1: James Bond
    column 2: 007 building
    column 3: Bond street
    column 4: London

    Any ideas?

    Krunal

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Without a pre-defined number of lines I can't think of any way other than dynamic SQL (the solution is already overkill without that!).
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem with parsing a generic pre-formatted "postal expression" is that even the UPU can't do it automatically. There are (or at least were) 172 components, many of which are mutually exclusive and most of which require some level of AI to recognize.

    If you stick to one country or postal region, this becomes much simpler but still essentially an AI project.

    I wouldn't attempt this on a production system using just SQL.

    -PatP

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I can think of a convoluted way of doing this on SQL Server 2005 but only if we say there are a maximum of, e.g. 10 lines...

    It involves a couple of CTEs, one recursive, and a PIVOT of the final result.
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2008
    Posts
    5
    Hi Georgev,

    I don't think there would be more than 6 lines to be honest....

    if you have any idea of code that would be great!

    Krunal

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If I get the time at lunch I'll see if I can knock something up.
    Please bear in mind that it will be hideous and slow - so I suggest you use it to update a table instead appropriately instead of for presentation (i.e. store instead of select).
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Using a function I wrote earlier today (http://www.dbforums.com/showthread.php?t=1636266) I have been able to come up with something pretty reasonable
    Code:
    CREATE TABLE dbo.addresses (
       full_address varchar(300) NOT NULL
    )
    
    --Sample data
    INSERT INTO dbo.addresses(full_address)
      VALUES (
    'Mickey Mouse
    Mouse Street
    Disney Land
    Paris'
    )
    INSERT INTO dbo.addresses(full_address)
      VALUES (
    'James Bond
    007 Building
    Bond Street
    London'
    )
    INSERT INTO dbo.addresses(full_address)
      VALUES (
    'Oscar The Grouch
    Sesame Street'
    )
    INSERT INTO dbo.addresses(full_address)
      VALUES (
    'Homeless Joe'
    )
    INSERT INTO dbo.addresses(full_address)
      VALUES (
    'This
    Address
    Has
    More
    Than
    6
    Lines'
    )
    
    --Add our destination columns
    ALTER TABLE dbo.addresses
      ADD address_line_1 varchar(50)
        , address_line_2 varchar(50)
        , address_line_3 varchar(50)
        , address_line_4 varchar(50)
        , address_line_5 varchar(50)
        , address_line_6 varchar(50)
    
    DECLARE @delimiter char(1)
        SET @delimiter = Char(10) --Carriage return
    
    --Recursive CTE to find intances of delimiter
    ; WITH cte AS (
      SELECT 0 As [pos]
           , 1 As [level]
           , full_address
      FROM   dbo.addresses
        UNION ALL
          SELECT Convert(int, CharIndex(@delimiter, full_address, cte.pos + 1))
               , level + 1
               , full_address
          FROM   cte
          WHERE  CharIndex(@delimiter, full_address, cte.pos + 1) > 0
    )
    --Update our destination columns with pivoted values
    UPDATE dbo.addresses
    SET    address_line_1 = x.[1]
         , address_line_2 = x.[2]
         , address_line_3 = x.[3]
         , address_line_4 = x.[4]
         , address_line_5 = x.[5]
         , address_line_6 = x.[6]
    FROM   dbo.addresses As [a]
     INNER
      JOIN (
            SELECT full_address
                 , [1]
                 , [2]
                 , [3]
                 , [4]
                 , [5]
                 , [6]
            FROM   (
                    SELECT a.full_address
                         , a.level
                         , SubString(a.full_address, a.pos + 1, Coalesce(b.pos - 1, Len(a.full_address)) - a.pos ) As [value]
                    FROM   cte a
                     LEFT
                      JOIN cte b
                        ON a.level + 1 = b.level
                       AND a.full_address = b.full_address
                   ) As [source]
            PIVOT  (
                    Max(value)
                    FOR level IN ([1], [2], [3], [4], [5], [6]) --must explicitly declare the number of lines here
                   ) As [pivot_table]
           ) As [x]
        ON a.full_address = x.full_address
    
    --Remove our offensive column
    ALTER TABLE dbo.addresses
      DROP COLUMN full_address
    
    --Display results
    SELECT *
    FROM   dbo.addresses
    
    GO
    --Tidy up
    DROP TABLE dbo.addresses
    Obviously (ha!) remove the update (and all alter table statements) if you simply want to show the results of the PIVOT (i.e. derived values as opposed to propogated ones)
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    P.S. you owe me half a lunch break
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    wow...PIVOT

    impressive....but you are not a Jedi yet
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I thought the recursive CTE was the cute part to be honest
    George
    Home | Blog

  15. #15
    Join Date
    Dec 2008
    Posts
    5
    Mate you're a star!

    Cheers.

    Sorry for taking up your break, i'll defo return the favour some how.

    Krunal

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •