Problem translating dbl quotes with sql loader

[smheying]

I have a data file that has intermittent double quotation marks in it that I want to translate to something else, like a "-". I haver used the sqlldr and the TRANSLATE command to convert other values, but sqlldr seems to problem with the ".

Here is the command that I am using

WELL_NAME "translate(:WELL_NAME,'"','-')",

This is the error:

SQL*Loader-350 Syntax error at line 25.
Expecting ",' or ")", found ",".
WELL_NAME "translate(:WELL_NAME,'"','-')",

I have tried escaping the " with a \, but that did not work either.

Can anyone help?

[LKBrwn_DBA]

Try REPLACE instead:

WELL_NAME "REPLACE(:WELL_NAME,'"','-')",

[smheying]

REPLACE gets same results as TRANSLATE. It appears the problem is that as sqlldr parses the control file, it encounters the embedded double quotes, which assumes to be the end of the command string. Since our system apparently does not honor the \ as an escape char, is there some other way to embed the '"' in the command?

[LKBrwn_DBA]

Use CHR() conversion:

WELL_NAME "REPLACE(:WELL_NAME,CHR(34),'-')",

[smheying]

Good idea! That should work. I Hadn't thought of that. I'll give it a try next week when I am back in the office.

Thank you

[smheying]

Finally had a chance to try your suggestion: worked like a charm! Thank you.