sqlldr question

[Veeru71]

I have a table with 2 columns.....

CREATE TABLE TEST ( ID NUMBER(5), NAME VARCHAR2(30))

My data file looks like the following and '|' is the col delimiter

10| | -- single space
20| | -- 5 spaces
30|\ | -- back slash followed by a single space
40|| -- 0 spaces
50|JOHN | -- valid data with possible trailing spaces

My requirement is...
In the 'NAME' column,

1) if the data contains 1 or more spaces (eg. 1st & 2nd lines), it
should be loaded as a single space.
2) If the data contains '\ ' (i.e, backslash followed by a single
space, eg. 3rd line) it should be loaded as a single space
3) If there is no data (eg. 4th line), it should be loaded as NULL
4)Any other data should be right-trimmed (eg. 5 th line should be
loaded as 'JOHN' without any trailing spaces).

The following control file is working to some extent but it is unable
to load the 2nd line (more than 1 space) properly. Multiple spaces
are getting RTRIMmed to empty string and hence, a NULL is getting
loaded.

ctl file
======
load data
infile 'test.unl'
preserve blanks
into table TEST
fields terminated by '|'
trailing nullcols
(
ID,
NAME CHAR(30) "DECODE(:NAME, ' ', ' ', '\\\ ', ' ', RTRIM(:NAME))"
)

Any thoughts ?
Thanks in advance
- Murty

[Ed Prochak]

consider trimming the name before the decode? Or would substring work?
DECODE(SUBSTR(:NAME,1,2), ' ', ' ', '\\\ ', ' ', ' ', ' ',
RTRIM(:NAME))
this assumes no left padding (i.e., no input has two leading spaces
followed by nonspace characters.

Actually, I find it a LOT easier to load into a staging table then
parse the data in PL/SQL. I have also done preprocessing (in PERL or
other language with regular expressions) before loading. Now with
external tables, you may not need to create a regular table for
staging.

HTH,
Ed