Re: how to test a string for a valid date in sql
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sun, 13 Dec 2009 12:36:47 +0100
Message-ID: <7ok1ukF3hao2vU1_at_mid.individual.net>
On 11.12.2009 11:01, Jaap W. van Dijk wrote:
> I'm loading millions of records in a transformation step in a
> datawarehouse with an INSERT INTO ... SELECT ... FROM ... Part of the
> transformation is converting a date-string in a VARCHAR2 column into a
> date in a DATE column, something like
>
> CASE WHEN date-string is valid THEN TO_DATE(date-string) ELSE NULL
> END.
>
> Is it possible to test a string for a valid date in Oracle 9i/10g/11g
> without resorting to PL/SQL? If I use a homemade PL/SQL function, this
> is performed once for each record, which increases the load time
> considerably due to the millions of SQL - PL/SQL context switches back
> and forth.
Date: Sun, 13 Dec 2009 12:36:47 +0100
Message-ID: <7ok1ukF3hao2vU1_at_mid.individual.net>
On 11.12.2009 11:01, Jaap W. van Dijk wrote:
> I'm loading millions of records in a transformation step in a
> datawarehouse with an INSERT INTO ... SELECT ... FROM ... Part of the
> transformation is converting a date-string in a VARCHAR2 column into a
> date in a DATE column, something like
>
> CASE WHEN date-string is valid THEN TO_DATE(date-string) ELSE NULL
> END.
>
> Is it possible to test a string for a valid date in Oracle 9i/10g/11g
> without resorting to PL/SQL? If I use a homemade PL/SQL function, this
> is performed once for each record, which increases the load time
> considerably due to the millions of SQL - PL/SQL context switches back
> and forth.
Did you actually measure that or is that an assumption? Not sure whether the regexp approach does suffer from the very same problem.
Here's another sketch of an idea for the case that you actually have much less dates than rows: load all the dates with DISTINCT into a table with two columns: original string, converted date. Do an UPDATE using the custom PL/SQL function and finally do the import with a join against that table. Maybe you can even pull that off with a WITH clause. Of course, there are many variables in there so the approach might not be feasible at all in your case (e.g. index on the original text column helps).
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Sun Dec 13 2009 - 05:36:47 CST