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

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