List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:March 24 2005 8:02pm
Subject:Re: a very tricky string extraction
View as plain text  
Ed,


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
> else
> 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
YY-MM-DD.

>  
> 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.


HTH,
Joerg Bruehe

-- 
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

Thread
a very tricky string extractionEd Reed24 Mar
  • Re: a very tricky string extractionDan Nelson24 Mar
    • Re: a very tricky string extractionKeith Ivey24 Mar
  • Re: a very tricky string extractionKeith Ivey24 Mar
  • Re: a very tricky string extractionJoerg Bruehe24 Mar
  • Re: a very tricky string extractionSGreen24 Mar
RE: a very tricky string extractionJay Blanchard24 Mar
RE: a very tricky string extractionJay Blanchard24 Mar
Re: a very tricky string extractionEd Reed24 Mar
  • Re: a very tricky string extractionSGreen24 Mar
Re: a very tricky string extractionEd Reed24 Mar
  • Re: a very tricky string extractionEamon Daly24 Mar
    • Re: a very tricky string extractionKeith Ivey24 Mar
  • Re: a very tricky string extractionEamon Daly25 Mar
Re: a very tricky string extractionEd Reed24 Mar