let me put it in a "politically incorrect", blunt way:
Am Do, den 24.03.2005 schrieb Ed Reed um 20:49:
> This is an interesting problem that I hope someone can help me with. I
> have a varchar field that contains data like this,
> 01/01/05 SG Reviewed this
> 12/15/03 DSD Reviewed that
> 10/24/02 EWW Worked on that and tested this then stop to do something
> 05/02/01 AW Did something
> 08/31/98 DSD Tested this
> 07/22/97 EWW Worked on that and did something
The MM/DD/YY format of dates is something I would (try to) avoid
everywhere if the slightest probability (danger) exists an ordering by
this value would ever be needed.
I do not care about separators, but there is a reason for the ISO format
> I need a Select statement that returns the Date for the first occurance
> of the 'Tested this' substring
Do you have the possibility to add a separate "date" column?
IMO, that would be the "clean" way of achieving your goal.
> So far what I've come up with doesn't quite get what I need and it's
> already pretty hairy. I wonder if there's a more elegant way that I'm
> unaware of.
I have no idea how to do it all in SQL, unless you add a pile of
substring extraction / assembly calls (and rely on all dates keeping to
the format of your examples).
It would be safer to let (My)SQL just do the filtering for 'Tested this'
and code the sorting (and restriction to the earliest match) in your
application, where you have better chances of checking that the dates
really are written in the same format.
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com