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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With