pattern matching for sqlldr

[zapster]

hi all



is there a way to pattern match the input file in the "when" clause part of the sqlldr

if so how?

ie

load data
replace
into table table_name
when (01:04 = ???????????????)
(
column_name POSITION(01:05) CHAR(5)
)


the first four characters must be:
position 1 -- a char of A to D
position 2 -- a char of R to Z
position 3 -- a number 0 to 9
position 4 -- a number 0 to 9

thanks
for the help

zapster

 

[rcurva]

I would suggest for you to load everything into a staging table first and do your patter matching using a stored procs or PL/SQL. How many records are you loading by the way?

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[zapster]

Robbie

I'm loading about 28000 records but there extra rows in the data file that need to be filtered out

hence the pattern matching idea

zapster

 

[bjorneh]

Hi

I'd use WHEN clause with WHEN (01:04) > and (01:04) < 10
I think that's possible, but if it isn't, then you could also just duplicate the WHEN clause for explicitly matching the records you are after. (After all, a wildcard is effectively not having a WHEN clause at all)

 

[karluk]

I don't think the SQL*Loader &quot;when&quot; clause is flexible enough to do what you want. Multiple conditions in the when clause must be joined with &quot;and&quot;, and the only conditions that can be tested are &quot;equals&quot; and &quot;not equals&quot;.

As an alternative, you could create a check condition on the column. That would prevent rows that violate your pattern to be rejected.

alter table your_table add constraint ck_your_column
(substr(your_column,1,1) between 'A' and 'D'
and
substr(your_column,2,1) between 'R' and 'Z'
and
substr(your_column,3,1) between '0' and '9'
and
substr(your_column,4,1) between '0' and '9')