List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 24 2005 9:18pm
Subject:Re: a very tricky string extraction
View as plain text  
"Ed Reed" <EReed@stripped> 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 <keith@stripped> 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 < keith@stripped >
> 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
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