SQL Genius
Every so often, you run across somebody doing something really clever with SQL. I ran across a fantastic example this morning on the SQLite mailing list:
In oracle there is the TO_DATE function which accepts at least two
arguments: a field convertible to string, and a string defining the date
format of the first field. This format string involves portions of date
strings like 'YYYY-MM-DD HH24:MI:SS' , our standard sortable no-timezone
date format that we use. But users, being users, like to enter in dates in
just about any format, so we allow other formats like 'MM/DD/YYYY' and
'MM/DD/YY' (being primarily in america, we give priority to the
month/day/year format, instead of the european day/month/year).
We then convert our input string into letter-number combinations using
TRANSLATE so that 0-9 become 9, and A-Z become Z. Then in any_date_format
we have patterns like:
date_format=> 'YYYY-MM-DD HH24:MI:SS', date_pattern => '9999-99-99
99:99:99'
and we select the date_format column based on the likeness of the
date_pattern column, run it through to_date, catch any exceptions, and
return the DATE column.
I always find this type of idea both amusing and awe-inspiring, because thinking of these tricks requires the ability to look at things differently. I think that's particularly hard to do with SQL because it's used to do the same general mundane things so often that the whole language becomes subconscious, making it really hard to think creatively. Maybe that's what defines genius though.
Trackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Add Comment