Re: SQL*Load query

Why don't you get the data into a fixed format, and then load the data positionally? This is the easiest way to break apart long fields.

Tim Scott <tscott_at_fdgroup.co.uk> wrote :
> I have a situation like the one below in that I need to split up a
> character field into multiple fields where they are longer than the
> field size.
>
> It's harder to describe than to example, so:
>
> ------------------------------
> load data
> infile *
>
> into table tempload
> truncate
> fields terminated by '*' optionally enclosed by '"'
> trailing nullcols
> (fkey,
> recid,
> abs1 position (*) char(5),
> abs2 position (*) char(5),
> abs3 position (*) char(5),
> abs4 position (*) char(5))
>
> begindata
> TT*1*abcde*
> TT*2*abcdeABCDE*
> TT*37*"abcdeAB*DEZYXWV"*
> TT*4*"abcdeAB""DEzyxwvZYXWV"*
> ------------------------------
>
> Should produce 4 rows:
>
> FKEY RECID ABS1 ABS2 ABS3 ABS4
> ------- ------- ------- ------- ------- -------
> TT 1 abcde
> TT 2 abcde ABCDE
> TT 37 abcde AB*DE ZYXWV
> TT 4 abcde AB"DE zyxwv ZYXWV
>
> Instead I get (in log file):
 [list of errors deleted]

-- 
Peaches	  http://miso.wwa.com/~fsgchi 
	  reply to: fsgchi at wwa dot com
What lies before us, and what lies behind us, are tiny matters
compared to what lies within us...	--Ralph Waldo Emerson