Skip to Main Content
  • Questions
  • Replace backslash and double quotes inside a string using sqlldr

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Marcelo.

Asked: May 22, 2017 - 8:00 pm UTC

Last updated: May 24, 2017 - 3:11 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom.
I need your help.

I'm trying to loader data using sqlldr to an Oracle 11g database.

My data has a column that sometimes comes like below:

,"\"Taiwan Mobile\"",

My ctl has:
FIELDS TERMINATED BY ","
optionally enclosed by '"'

I've been tried many types of replace and regexp_replace and I continue getting no solution.

My replaces works fine in SQL Developer, but it doesn't work in SQLLDR.

Examples of replaces used with both replace and regexp_replace:

, column "REPLACE(:column,'\"')"
, column "REPLACE(:column,'\\"')"
, column "REPLACE(:column,'\\\"')"
, column "REPLACE(:column,'"')"

In any case I got errors in my bad file at this column!!
Could you help me?

and Connor said...

I'd use an external table

SQL> CREATE TABLE t (
  2    x      VARCHAR2(50)
  3  )
  4  ORGANIZATION EXTERNAL (
  5    TYPE ORACLE_LOADER
  6    DEFAULT DIRECTORY temp
  7    ACCESS PARAMETERS (
  8      RECORDS DELIMITED BY NEWLINE
  9      FIELDS TERMINATED BY ','
 10      MISSING FIELD VALUES ARE NULL
 11      (
 12        x CHAR(50)
 13      )
 14    )
 15    LOCATION ('t.dat')
 16  );

Table created.

SQL>
SQL>
SQL> select * from t;

X
--------------------------------------------------
"\"Taiwan Mobile\""

SQL>
SQL> select replace(trim('"' from replace(x,'\"',chr(0) )),chr(0),'"')
  2  from t;

REPLACE(TRIM('"'FROMREPLACE(X,'\"',CHR(0))),CHR(0)
--------------------------------------------------
"Taiwan Mobile"



Is this answer out of date? If it is, please let us know via a Comment