NULLIF in SQL Loader to compare a string value

问题:

I have a file with simple rows that I want to upload into a table using sql loader.

However, there is a column that contain values that I want to exclude:

e.r.

123;Código Postal *;HH;456
523;Código Postal *;HI;459
723;Código Postal *;HM;450

The column that I want to exclude and asign to NULL is the value "Código Postal *".

I want to modify my control file to exclude this value, but is not possible.

I have used into control file:

 EC_CONS_ZIP_CODE  "DECODE(:EC_CONS_ZIP_CODE,'Código Postal%',NULL,:EC_CONS_ZIP_CODE)"

But doesn't work because I have a special character ("*" and "ó")

Is there a way to avoid this kind of character to use it or another way?

Is it possible to assign a pattern for this word using Control file?

Regards!!

回答1:

Set character set to utf8 in the control file

CHARACTERSET UTF8

Another way is to use a regex

EC_CONS_ZIP_CODE  " REGEXP_REPLACE(:EC_CONS_ZIP_CODE,'C.digo Postal.*','')"

Or try with the specific Unicode encoding for ó (00F3)

EC_CONS_ZIP_CODE  " REGEXP_REPLACE(:EC_CONS_ZIP_CODE,'C' || unistr('\00F3')|| 'digo Postal.*','')"

回答2:

Mark that column as FILLER in the control file to ignore that field in the file and save the overhead of calling REGEXP_REPLACE for every row. Oracle will populate the NULL for you:

...
EC_CONS_ZIP_CODE FILLER,
...