Geek Answers Handbook

Split an address column into separate columns in an SQL view

I have a “Address” column in a table that I need to split into several columns in a view in SQL Server 2005. I need to split the column into a row character, chr (10), and can be 1 to 4 rows (0 to 3 rows) in a column. Below are a few examples of what I need to do. What is the easiest way to do this?

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.
+5
source share
3 answers

this will split the address using the parseame function and combining it with COALESCE to get the right information in the right column

If you have more than 4 lines, this method will NOT work

edit: added code to cancel 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
+3

... , , , . , , , , ( , "" , ), , /, , .

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(), , , , - . (^), , , , , .

, . , ... , .

+1

SQL . - , csv , Perl/PHP/Python. , .

0
source

More articles:


All Articles