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" <EReed@stripped>
To: <mysql@stripped>
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
>
>>>> <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
>
>

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.