Re: SQL*Loader Control File

rss_vishnu_at_yahoo.com wrote:
> 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..
>
> Any inputs are highly appreciated.
>
> Thanks,
> Vishnu
>

When you try:

SQL> select
[Quoted] substr(substr('?foo_at_bar.com#',2),1,length('?foo_at_bar.com#')-2) "Result" from dual;

Result

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.

--

Arto Viitanen
Finland