Re: HELP: loading strings with embedded escaped "

In comp.databases.oracle.misc Furkan Khan <khanf_at_home.com> wrote:
: Folks, I am trying to use sql*loader to load
: string that have embedded escaped
: double quotes (\").

: I have the delimiter defined as:
: FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

: data looks like:
: 1111,"here it is \"new\" ...",9000,............

There is a bug (or feature) in SQL*Loader that makes handling escaped "s either by \" or "" (MS Excel). I have gotton SQL*loader to strip extranikous quotes by using tab delimited files and the following line N which handles "s and either end and makes ""s into "s:

 PART_NAME "LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(:PART_NAME,CHR(34)||CHR(34),CHR(1)),CHR(34)),CHR(1),CHR(34))))", First, SQL*Loader want let you literally put "s inside the "..." function string so I use CHR(34) to bypass this. From inside out the following is done:

1. Convert all "" pairs to an unused character, I used Ctrl-A.
2. Remove all remaining single occurrances of "s.
3. Convert Ctrl-A to juat a single ".
4. Trim whitespace.

This may not work in your case since you are using comma delimited strings. Commas can appear inside your strings. But this may have get on the right track.

Good luck,
Steven
--

                 _|_  |  _|_   "I am the way and the truth and the life.
                  | --|-- |     No one comes to the Father except through
Steven Whatley    |   |   |      me.  If you really knew me, you would
Houston, Texas        |           know my Father as well.  From now on,
swhatley_at_blkbox.com   |            you do know him and have seen him."
http://www.blkbox.com/~swhatley/        -- Jesus Christ (John 14:6-7 NIV)