An issue with REPLACE function in SQL Loader
244101Feb 29 2008 — edited Feb 29 2008Hi Folks,
I am a having an issue with REPLACE function while using it in SQLLOADER.
I have a control file that captures each field from a data file in different variables. In one such field (Project Description) we have the data as below:
"Project
Info Mod
Test" where the new line character is being introduced.
While capturing, in the control file, it we are defining it as
Project_Description "replace(rtrim(:Project_Description),chr(10))",
After this the same is loaded into a staging table but seems that there are 3 records created, one for each line.
I believe the replace is not working. But using SQL Plus the following query
select replace(replace(replace('"Project
Info Mod
Test"
',chr(10)),chr(13)),chr(34)) from dual
yields the following output
Project Info ModTest
This output is what I wanted into the project description field.
Can someone throw some light on how to circumvent this issue? Your response is much appreciated.
Thanks
uds