Carriage Contron in field

I am trying to split an address field which contains carriage controls. The address field can have 2 carriage controls in it, ie 3 address lines. I want to split the address into 3 seperate colomns based on the control carriage. I know how to combine 3 co
lumns into 1 but not how to split it out again.
Chard.
hi chard,
I assume you have carriage return in the string data which is represented by
char(13). See following example.
--sample data
create table t(col1 varchar(400))
insert into t values ('address1' + char(13)+ 'address2' + char(13) +
'address3')
insert into t values ('address4' + char(13)+ 'address5' + char(13) +
'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6xbbhghhw')
--query
select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
substring (col1, charindex (char(13), col1) + 1,
(charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
(char(13), col1))) '2nd_address',
right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
from t
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Hi Vishal,
Exactly what I was after. Thanks very much.
Chard.
"Vishal Parkar" wrote:

> hi chard,
> I assume you have carriage return in the string data which is represented by
> char(13). See following example.
> --sample data
> create table t(col1 varchar(400))
> insert into t values ('address1' + char(13)+ 'address2' + char(13) +
> 'address3')
> insert into t values ('address4' + char(13)+ 'address5' + char(13) +
> 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6xbbhghhw')
>
> --query
> select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
> substring (col1, charindex (char(13), col1) + 1,
> (charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
> (char(13), col1))) '2nd_address',
> right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
> from t
>
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>