Re: how to test a string for a valid date in sql

From: Robert Klemme <>
Date: Sun, 13 Dec 2009 15:39:46 +0100
Message-ID: <>

On 13.12.2009 14:21, Gerard H. Pille wrote:
> Robert Klemme wrote:

>> 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).
> Context switches are very measurable, as is the number of times a stored 
> procedure or function is called.

Btw I did not ask whether they are measurable as such but whether OP had _verified_ that these context switches are actually an issue in his situation. While a context switch does have a price it may turn out that other approaches do have higher prices in particular situations. The only way to _know_ it is usually to test and measure.

> A table lookup will never beat a
> couple of lines of sql "logic".

When it comes to using words like "never" and "always" I am very cautious. Next someone might turn up with an example where a table lookup beats what you call "SQL logic" by an order of magnitude.



remember.guy do |as, often| as.you_can - without end
Received on Sun Dec 13 2009 - 08:39:46 CST

Original text of this message