How to split an Address text column into multiple columns

Question

I have one text column where the users type in Addresses. I need to display/split the addresses into multiple columns to include in a report.

The address data is in a Text column and appears like below on the application/screen.

123 Stack Street
Holborn
London
EC1 2QW

Each address line ends with a carriage return but is displayed in one column on SQL Server 2008.

Any idea's how this can be achieved without complex code, variables etc?

I'd like to split at each carriage return into anew column.

Have you tried googling this? Or even looking on stack overflow? What problems are you having? What have you tried? — Matt, Feb 18 '14 at 21:30
Search `Split Delimited String` or `Parse comma separated string`... the only tricky bit is that instead of a comma, you have `CHAR(13)` as your delimiter. — Hart CO, Feb 18 '14 at 21:35
Hi Matt, I Googled and Googled to no avail. Most of the solution are available did not meet my criteria, in that they have to handle a TEXT column and i wanted to create a View for others to use for reporting. I played with CharIndex, PatIndex but could not get it to work. I was hoping for a solution using these with len and substring. — user3306489, Mar 06 '14 at 15:36

score 2 · Answer 1 · answered Feb 18 '14 at 22:00

CREATE function [dbo].[SplitString] 
(
    @str nvarchar(max), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    p-1 ItemIndex,
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS Item
from tokens
);

Then execute it like this

select ItemIndex,Item
from SplitString('123 Stack Street
    Holborn
    London
    EC1 2QW',CHAR(13))

Hi Thanks for this, but how do i execute this against a column that holds the addresses? — user3306489, Mar 06 '14 at 15:34

score 0 · Answer 2 · edited May 23 '17 at 11:44

Test Data

DECLARE @TABLE TABLE ([Address] [VARCHAR](4000))
INSERT INTO @TABLE VALUES
('123' + CHAR(13) + 'Stack Street'+ CHAR(13) + 'Holborn' 
                   + CHAR(13) + 'London'+ CHAR(13) + 'EC1 2QW'),
('456' + CHAR(13) + 'OverFlow Street'+ CHAR(13) + 'Bolton' 
        + CHAR(13) + 'Greater Manchester'+ CHAR(13) + 'M1 6lML')

Query

SELECT * 
FROM
(
SELECT  DENSE_RANK() OVER ( ORDER BY [Address]) AS rn
      ,'Address Line' + CAST(ElementID AS NVARCHAR(10)) AS AddressLines
      , Element
FROM @TABLE
            CROSS APPLY dbo.func_Split([Address], CHAR(13))c
)Q
PIVOT (MAX(Element)
      FOR AddressLines
      IN ([Address Line1],[Address Line2],[Address Line3]
             ,[Address Line4],[Address Line5])
      )p

Result Set

╔════╦═══════════════╦═════════════════╦═══════════════╦════════════════════╦═══════════════╗
║ rn ║ Address Line1 ║  Address Line2  ║ Address Line3 ║   Address Line4    ║ Address Line5 ║
╠════╬═══════════════╬═════════════════╬═══════════════╬════════════════════╬═══════════════╣
║  1 ║           123 ║ Stack Street    ║ Holborn       ║ London             ║ EC1 2QW       ║
║  2 ║           456 ║ OverFlow Street ║ Bolton        ║ Greater Manchester ║ M1 6lML       ║
╚════╩═══════════════╩═════════════════╩═══════════════╩════════════════════╩═══════════════╝

Split Function

I have used a split function in my solution see here for the definition of Sql Server Split function

Matt · Answer 3 · 2014-02-19T15:28:12.917

I've always preferred the xml and back to any of the other options. I also noticed you stated "TEXT" so I went ahead and did the text to varchar(max) conversion for you in the first table expression that does the REPLACE.

Essentially replace all CHAR(13)'s with a end/begin xml tag. Wrap that in XML. Output it as XML. Shred it back to a table in a manner you see fit.

DECLARE @Tmp TABLE (Id INT,Name TEXT) 
INSERT @Tmp SELECT 1,'123 Stack Street' + CHAR(13) + 'Holborn' + CHAR(13) + 'EC1 2QW' 

DECLARE @XML XML = 
(
    SELECT  T.Id AS "@ID",
          CONVERT(XML,'<PART>' + REPLACE(CAST(Name AS VARCHAR(max)),CHAR(13),'</PART><PART>') + '</PART>') AS AddressParts
    FROM      @Tmp AS T
    FOR XML PATH('Name'), ROOT('Names'), ELEMENTS, TYPE
)

SELECT  Address1 = FieldAlias.value('(AddressParts/PART)[1]','varchar(max)'),
       Address2 = FieldAlias.value('(AddressParts/PART)[2]','varchar(max)'),
       Address3 = FieldAlias.value('(AddressParts/PART)[3]','varchar(max)'),
       Address3 = FieldAlias.value('(AddressParts/PART)[4]','varchar(max)')
FROM       @XML.nodes('//Name') AS S(FieldAlias)

score 0 · Answer 4 · answered Aug 27 '19 at 08:33

If using sql server 2016(question was 5 years ago) or later version then you have https://learn.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-2016 available. Just replace carriage return character with "." character first. Works for maximum of 3-4 carriage return on each row.

How to split an Address text column into multiple columns

4 Answers4