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

>>> <SGreen@stripped> 3/24/05 1:18 PM >>>
"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