Contractor Name, Address, and Zip Code
YORK INTERNATIONAL CORPORATION
631 S RICHLAND AVE
Contract_v = varchar2 ''contractor,name,and Zipcode);
Address_v = null;
Above is text file. want to read it in Oracle (preferably)or SQL Server.After reading Contractor Name,address,and Zip Code, I want to substring /concat all 4 lines (including tabs) and remove coma after YORKand put all 4 lines in as one field called Address_v and add ',' at the end (after zip code).
Thanks for the help.
You can use
to read the text file in ur PL/SQL
if you are using 9i use external table
this shuld be easy for ur requirement
1) create a external table with these Contractor Name, Address, and Zip Code columns.
2) write a select statement:
SELECT Contractor_Name || REPLACE(Address,',','') || Zip_Code || ',' INTO lv_Address_v FROM external_table_name;
insert into Address_v values(lv_Address_v);
try this hope this help U
"remove coma after YORK" -- in don't understand this paret. i hope u want to remove from address column.
Click Here to Expand Forum to Full Width