Re: a very tricky string extraction
(Our mail server just flaked out, so apologies if this is a
duplicate.)
That doesn't work for me (I get '12/15/03'), but I like the
way you think, pardner. How about:
SELECT
LEFT(SUBSTRING_INDEX(LEFT(log, LOCATE('Tested this', log)), '\n', -1), 8)
FROM test WHERE log like '%tested this%';
As mentioned by others in the thread, this is fine as a
stopgap, but you should change your table ASAP.
Eamon Daly
- Original Message -
From: "Keith Ivey" <[EMAIL PROTECTED]>
To:
Cc: "Ed Reed" <[EMAIL PROTECTED]>
Sent: Thursday, March 24, 2005 3:53 PM
Subject: Re: a very tricky string extraction
Another possibility:
SELECT SUBSTRING(log, LOCATE('\n', SUBSTRING(log, 1,
LOCATE('Tested this', log)-1))+1, 8)
FROM test WHERE LOCATE('Tested this', log);
--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a very tricky string extraction
WHOLLY COW!!!
That was awesome. Thanks
- Ed
>>> "Eamon Daly" <[EMAIL PROTECTED]> 3/24/05 1:48 PM >>>
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(,)
>
> Then you could program the UDF to find various 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
>
>
Re: a very tricky string extraction
Eamon Daly wrote:
SELECT
LEFT(SUBSTRING_INDEX(LEFT(log, LOCATE('\n', log, LOCATE('tested this',
log)) - 1), '\n', -1), 8)
FROM test WHERE log LIKE '%tested this%'
Another possibility:
SELECT SUBSTRING(log, LOCATE('\n', SUBSTRING(log, 1,
LOCATE('Tested this', log)-1))+1, 8)
FROM test WHERE LOCATE('Tested this', log);
--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a very tricky string extraction
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:
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(,)
Then you could program the UDF to find various 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]
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(,) Then you could program the UDF to find various 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
Re: a very tricky string extraction
"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(,) Then you could program the UDF to find various 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
Re: a very tricky string extraction
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
Re: a very tricky string extraction
"Ed Reed" <[EMAIL PROTECTED]> wrote on 03/24/2005 02:49:30 PM: > This is an interesting problem that I hope someone can help me with. I > have a varchar field that contains data like this, > > 01/01/05 SG Reviewed this > 12/15/03 DSD Reviewed that > 10/24/02 EWW Worked on that and tested this then stop to do something > else > 05/02/01 AW Did something > 08/31/98 DSD Tested this > 07/22/97 EWW Worked on that and did something > > I need a Select statement that returns the Date for the first occurance > of the 'Tested this' substring > > So far what I've come up with doesn't quite get what I need and it's > already pretty hairy. I wonder if there's a more elegant way that I'm > unaware of. > > Thanks I think you need to extract those columns during data import. That way you have date values in a date column, the initials in their own column, and the actions in a column all their own. It will make this a much more manageable design. If you already have this in the database, then I would reprocess the table and split your data up into separate columns. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: a very tricky string extraction
[snip] [snip] 01/01/05 SG Reviewed this 12/15/03 DSD Reviewed that 10/24/02 EWW Worked on that and tested this then stop to do something else 05/02/01 AW Did something 08/31/98 DSD Tested this 07/22/97 EWW Worked on that and did something I need a Select statement that returns the Date for the first occurance of the 'Tested this' substring [/snip] That is a bad way to keep a date, but tryI am not sure you can use MIN here...NOT TESTED SELECT MIN(SUBSTRING(myField, 1, 8)) FROM myTable; [/snip] With the WHERE condition of course -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: a very tricky string extraction
[snip] 01/01/05 SG Reviewed this 12/15/03 DSD Reviewed that 10/24/02 EWW Worked on that and tested this then stop to do something else 05/02/01 AW Did something 08/31/98 DSD Tested this 07/22/97 EWW Worked on that and did something I need a Select statement that returns the Date for the first occurance of the 'Tested this' substring [/snip] That is a bad way to keep a date, but tryI am not sure you can use MIN here...NOT TESTED SELECT MIN(SUBSTRING(myField, 1, 8)) FROM myTable; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a very tricky string extraction
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a very tricky string extraction
Ed, let me put it in a "politically incorrect", blunt way: Am Do, den 24.03.2005 schrieb Ed Reed um 20:49: > This is an interesting problem that I hope someone can help me with. I > have a varchar field that contains data like this, > > 01/01/05 SG Reviewed this > 12/15/03 DSD Reviewed that > 10/24/02 EWW Worked on that and tested this then stop to do something > else > 05/02/01 AW Did something > 08/31/98 DSD Tested this > 07/22/97 EWW Worked on that and did something The MM/DD/YY format of dates is something I would (try to) avoid everywhere if the slightest probability (danger) exists an ordering by this value would ever be needed. I do not care about separators, but there is a reason for the ISO format YY-MM-DD. > > I need a Select statement that returns the Date for the first occurance > of the 'Tested this' substring Do you have the possibility to add a separate "date" column? IMO, that would be the "clean" way of achieving your goal. > > So far what I've come up with doesn't quite get what I need and it's > already pretty hairy. I wonder if there's a more elegant way that I'm > unaware of. I have no idea how to do it all in SQL, unless you add a pile of substring extraction / assembly calls (and rely on all dates keeping to the format of your examples). It would be safer to let (My)SQL just do the filtering for 'Tested this' and code the sorting (and restriction to the earliest match) in your application, where you have better chances of checking that the dates really are written in the same format. HTH, Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a very tricky string extraction
Ed Reed wrote: 01/01/05 SG Reviewed this 12/15/03 DSD Reviewed that 10/24/02 EWW Worked on that and tested this then stop to do something else 05/02/01 AW Did something 08/31/98 DSD Tested this 07/22/97 EWW Worked on that and did something I need a Select statement that returns the Date for the first occurance of the 'Tested this' substring That's the sort of thing you're going to be better off doing in Perl or PHP or whatever applicationn language you're using rather than trying to handle in your MySQL query. And of course if it's something you're doing regularly, rather than a one-time conversion, you should put that data into a proper table, with date, initials, and description as columns. If you're determined to do it, you'll need a bunch of applications of LOCATION() and SUBSTRING(), and probably IF(). -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a very tricky string extraction
In the last episode (Mar 24), Ed Reed said: > This is an interesting problem that I hope someone can help me with. I > have a varchar field that contains data like this, > > 01/01/05 SG Reviewed this > 12/15/03 DSD Reviewed that > 10/24/02 EWW Worked on that and tested this then stop to do something else > 05/02/01 AW Did something > 08/31/98 DSD Tested this > 07/22/97 EWW Worked on that and did something > > I need a Select statement that returns the Date for the first > occurance of the 'Tested this' substring > > So far what I've come up with doesn't quite get what I need and it's > already pretty hairy. I wonder if there's a more elegant way that I'm > unaware of. How about: SELECT LEFT(description, 8) FROM mytable WHERE description LIKE "%tested this%" LIMIT 1 -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a very tricky string extraction
This is an interesting problem that I hope someone can help me with. I have a varchar field that contains data like this, 01/01/05 SG Reviewed this 12/15/03 DSD Reviewed that 10/24/02 EWW Worked on that and tested this then stop to do something else 05/02/01 AW Did something 08/31/98 DSD Tested this 07/22/97 EWW Worked on that and did something I need a Select statement that returns the Date for the first occurance of the 'Tested this' substring So far what I've come up with doesn't quite get what I need and it's already pretty hairy. I wonder if there's a more elegant way that I'm unaware of. Thanks
