Split Address column into separate columns in SQL view

Question

I have an Address column in a table that I need to split into multiple columns in a view in SQL Server 2005. I need to split the column on the line feed character, chr(10), and there could be from 1 to 4 lines (0 to 3 line feeds) in the column. Below are a couple of examples of what I need to do. What is the simplest way to make this happen?

Examples:

Address                 Address1      Address2       Address3            Address4
------------        =   -----------   -----------    -----------------   ---------
My Company              My Company     123 Main St.  Somewhere,NY 12345  
123 Main St.         
Somewhere,NY 12345

Address                 Address1       Address2      Address3      Address4
------------        =   ------------   ----------    -----------   ---------
123 Main St.            123 Main St.

Solution 1

this will split the address by using the parsename function and combining that with COALESCE to grab the correct info in the correct column

if you have more than 4 lines this method will NOT work

edit: added the code to reverse the order

    create table #test (address varchar(1000))

    --test data
    insert #test values('My Company
    123 Main St.         
    Somewhere,NY 12345')

    insert #test values('My Company2
    666 Main St.  
    Bla Bla       
    Somewhere,NY 12345')

    insert #test values('My Company2')

    --split happens here
                            select
replace(parsename(address,ParseLen +1),'^','') as Address1,
replace(parsename(address,ParseLen ),'^','') as Address2,
replace(parsename(address,ParseLen -1),'^','') as Address3,
replace(parsename(address,ParseLen -2),'^','') as Address4
from(
select case  ascii(right(address,1)) when 10 then
replace(replace(left(address,(len(address)-1)),'.','^'),char(10),'.')  
else 
replace(replace(address,'.','^'),char(10),'.') end as address,
case  ascii(right(address,1)) when 10 then
len(replace(replace(address,'.','^'),char(10),'.')) -
len(replace(replace(address,'.','^'),char(10),'')) -1
else
len(replace(replace(address,'.','^'),char(10),'.')) -
len(replace(replace(address,'.','^'),char(10),'')) end as ParseLen
 from #test) x

Solution 2

This is awfully nasty... I strongly recommend that if you want to treat each address line separately, that you store it correctly in the first place. Instead of continuing to do what you're doing, add the additional columns, fix the existing data once (instead of "fixing" it every time you run a query), and then adjust the stored procedure that does the insert / update so that it knows to use the other columns.

DECLARE @Address TABLE(id INT IDENTITY(1,1), ad VARCHAR(MAX));

INSERT @Address(ad) SELECT 'line 1
line 2
line 3
line 4'
UNION ALL SELECT 'row 1
row 2
row 3'
UNION ALL SELECT 'address 1
address 2'
UNION ALL SELECT 'only 1 entry here'
UNION ALL SELECT 'let us try 5 lines
line 2
line 3
line 4 
line 5';

SELECT
    id,
    Line1 = REPLACE(REPLACE(COALESCE(Line1, ''), CHAR(10), ''), CHAR(13), ''),
    Line2 = REPLACE(REPLACE(COALESCE(Line2, ''), CHAR(10), ''), CHAR(13), ''),
    Line3 = REPLACE(REPLACE(COALESCE(SUBSTRING(Rest, 1, COALESCE(NULLIF(CHARINDEX(CHAR(10), Rest), 0), LEN(Rest))), ''), CHAR(10), ''), CHAR(13), ''),
    Line4 = REPLACE(REPLACE(COALESCE(SUBSTRING(Rest, NULLIF(CHARINDEX(CHAR(10), Rest) + 1, 1), LEN(Rest)), ''), CHAR(10), ''), CHAR(13), '')
FROM

(
    SELECT 
        id,
        ad,
        Line1,
        Line2 = SUBSTRING(Rest, 1, COALESCE(NULLIF(CHARINDEX(CHAR(10), Rest), 0), LEN(Rest))),
        Rest = SUBSTRING(Rest, NULLIF(CHARINDEX(CHAR(10), Rest) + 1, 1), LEN(Rest))
    FROM
    (
        SELECT
            id,
            ad,
            Line1 = SUBSTRING(ad, 1, COALESCE(NULLIF(CHARINDEX(CHAR(10), ad), 0), LEN(ad))),
            Rest = SUBSTRING(ad, NULLIF(CHARINDEX(CHAR(10), ad) + 1, 1), LEN(ad))
        FROM
            @address
    ) AS x
) AS y
ORDER BY id;

Denis' PARSENAME() trick is much tidier of course, but you have to be extremely careful about using a replacement character that is truly impossible to appear in the data naturally. The carat (^) is probably a good bet, but like I said, you need to be careful.

There are also software packages out there that are really good at scrubbing address and other demographic data. But cleaning up the data entry is the most important thing here that I'll continue to stress... if each address line needs to be treated separately, then store them that way.