Re: a very tricky string extraction

[Eamon Daly]

SELECT
LEFT(SUBSTRING_INDEX(LEFT(log, LOCATE('\n', log, LOCATE('tested this', 
log)) - 1), '\n', -1), 8)
FROM test WHERE log LIKE '%tested this%'

Don't try this at home.
____________________________________________________________
Eamon Daly

----- Original Message ----- 
From: "Ed Reed" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, March 24, 2005 3:37 PM
Subject: Re: a very tricky string extraction


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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]