We are running MS SQL Server 2000 as the backend for our ERP system. The customer database stores the customer address (ie. 1,2&3) as a single record with carriage returns and linefeeds, rather than breaking it into separate records. I am trying to write a query that will break the address field into a field for each line (at the carriage return or linefeed) so that we can link the UPS shipping computer directly to a OBDC query view of our sales orders.
The record currently looks something like:
Address:
Attn. Purchasing
Biggy Corporation
123 Some Street
I want it to look like:
Address1:
Attn. Purchasing
Address2:
Biggy Corporation
Address3:
123 Some Street
If someone could give me a few pointers or examples to work with on this, It would really save my neck.
create table address (ad varchar(100))
go
insert into address select 'Attn. Purchasing'
insert into address select 'Biggy Corporation'
insert into address select '123 Some Street'
go
alter table address add id int identity(1,1)
go
select 'Address'+convert(varchar,[id])+':'+char(13)+char(10)+ad+char(13)+char(10) from address
go
alter table address drop column id
go
--1.Create table with adds column
create table a(adds varchar(100))
--2.Insert value as you described
insert into a select 'Attn. Purchasing
Biggy Corporation
123 Some Street'
--3.Seperate it to 3 lines
declare @no1 int
set @no1= (select charindex(char(13),adds) from a)
declare @no2 int
set @no2 =(select charindexchar13),adds,charindex(char(13),adds)+1) from a)
select rtrim(left(adds,@no1-1)) as [address1:],rtrim(ltrim(substring(adds,@no1+1,@no2-@no1))) as [address2:],ltrim(rtrim(right(adds,len(adds)-@no2-1))) as [address3:] from a
Your code snipit works for 1 record, but not for more than 1 since your select queries on the declarations return more than 1 value.
I think i can work it through and maybe figure it out... if you happen to know something off hand... it would be much appreciated. Thanks for all your help so far.
Remove the declaration!And run only the following select statement!
select rtrim(left(adds,charindex(char(13),adds)-1)) as [address1:],
rtrim(ltrim(substring(adds,charindex char(13),adds)+1,charindex(char13),adds,charindex( char(13),adds)+1)-charindex(char(13),adds)))) as [address2:],
ltrim(rtrim(right(adds,len(adds)-charindex(char(13),adds,charindex(char(13),adds)+1 )-1))) as [address3:] from a
I dont know why you are getting such error.Maybe your table insert is not supported by using char(13) to delimite it.
Can you show me some address input you have in your table(maybe 10 rows is fine)
According to your real address data,we can see where is the problem from?