Thanks Shawn, The idea I've been working with on this is to use an InStr to find the point where the require substring appears. Then I need to search backwards from there to the point where the first \n\r is found. Then the Date that I want would be 8 characters from that position. The obvious problem is that there doesn't seem to be an easy way to search backwards through a string. Any ideas on that line of thought? Thanks - Ed
>>> <[EMAIL PROTECTED]> 3/24/05 1:18 PM >>> "Ed Reed" < [EMAIL PROTECTED] > wrote on 03/24/2005 04:02:28 PM: > Sorry everyone for not being more clear. The field IS in a multiline > varchar field. The example data was all from one record in the table. > > Unfortunately, this is a database that has been around for many years > and backward compatibility with other apps limits redesigning the table. > It is a Comments field and this is the first time anyone has ever tried > to mine any data from it. My app is a generic report writer that simply > takes and query string and returns the results. No processing of the > data can be done in the app. I need the result to come directly from > MySQL. > > Thanks again. > > - Ed > > >>> Keith Ivey < [EMAIL PROTECTED] > 3/24/05 12:04 PM >>> > Dan Nelson wrote: > > > How about: > > > > SELECT LEFT(description, 8) FROM mytable WHERE description LIKE > "%tested this%" LIMIT 1 > > Hmm, I assumed he was talking about a multi-line VARCHAR, but > now that I look again Dan's interpretation is probably the right > one. My previous message doesn't apply (except for the bit > about breaking it into columns if you're doing it regularly). > > -- > Keith Ivey < [EMAIL PROTECTED] > > Smokefree DC > http://www.smokefreedc.org > Washington, DC > Then I think you are stuck. What you are trying to find is a minimum value from a certain kind of row within a block of undelimited text. That's like hiding a whole table within a field and trying to write a query to find a field within the table within the field. Unless your text happens to be extremely well formatted, you have no chance of doing an extract in pure SQL and I would say this is definitely not possible using a single SQL statement. It may be possible in a single statement if you create a custom UDF that parses through that "comments" field. Suppose you wrote the UDF to use this API FIND_IN_COMMENTS(<part you want>,<field to search>) Then you could program the UDF to find various <part>s like "first test date", "last test date", "first review date", "First review person", etc. in any field that looks like your comments block. However, I believe that this kind of text manipulation and searching is more complex than can be easily achieved through just SQL and defintely too complex for a single statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
