Re: SQL*Loader Control File

Użytkownik Arto Viitanen napisał:

>

>> Hello GURUS!
>> I was just wondering is there any way to specify more than one value
>> for OPTIONALLY ENCLOSED BY option on SQL*Loader control file?
>>
>> Here is an example:
>> I have a file with thousands of do not contact email addresses, but
>> these email addresses are enclosed by different characters and I would
>> like to remove the enclosed characters before loading them into
>> database.
>>
>> Sample data:
>> ~email1_at_email.com~
>> #email2_at_email.com#
>> ^email3_at_email.com^
>> "email4_at_email.com"
>> 'email5_at_email.com'
>> etc..

> When you try:
>
> SQL> select
> substr(substr('?foo_at_bar.com#',2),1,length('?foo_at_bar.com#')-2) "Result"
> from dual;
>
> Result
> -----------
> foo_at_bar.com
>
> So I suggess you use external tables (provided you have Oracle 9 or
> newer). Then from the external table, you query
>
> select substr(substr(email,2),1,length(email)-2) from ex_table;
>
> If it shows ok, then just insert the select.
>
>

You can use 'substr(substr(:email,2),1,length(:email)-2)' in your control file as well.

-- 
Noel