How to do case sensitive replace with wild card matching?

How to do case sensitive replace with wild card matching?

am 24.12.2003 23:37:54 von mos

Ok, put your thinking caps on because this one bit of a toughie.

I an Update statement that will insert a '/' in front of the *first*
lowercase letter of a field value.
Example: "ABCDef" becomes "ABCD/ef".

Of course it doesn't always end in "ef" and could be any 2 or 3 group of
letters like "ABcde" becomes "AB/cde".

Any idea on how to do this? Or should I do it manually?

TIA

Mike



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: How to do case sensitive replace with wild card matching?

am 25.12.2003 07:24:05 von Michael Stassen

mos wrote:
> Ok, put your thinking caps on because this one bit of a toughie.
>
> I an Update statement that will insert a '/' in front of the *first*
> lowercase letter of a field value.
> Example: "ABCDef" becomes "ABCD/ef".
>
> Of course it doesn't always end in "ef" and could be any 2 or 3 group of
> letters like "ABcde" becomes "AB/cde".
>
> Any idea on how to do this? Or should I do it manually?
>
> TIA
>
> Mike

I assume you've already made sure that the column with the letters is
wide enough to hold an additional character.

Not sure what you mean by manually. I'd probably do it in perl, as it's
almost a one-liner ($field =~ s/([a..z])/\$1/). In mysql, though, the
problem seems to be that while we can match with a regular expression,
we can't replace with one. Hmm...

Perhaps we can take advantage of what we know about your data. I will
pretend your table is named "table1" and your column with the letters is
named "code". If I understand you correctly, the code column has some
number of capital letters followed by 2 or 3 lower case letters. If
that's true, I think we could do this in 2 updates, like this:

UPDATE table1
SET code=CONCAT(LEFT(code, CHAR_LENGTH(code)-2),'/',RIGHT(code,2))
WHERE code RLIKE BINARY '^[A-Z]+[a-z]{2}$';

UPDATE table1
SET code=CONCAT(LEFT(code, CHAR_LENGTH(code)-3),'/',RIGHT(code,3))
WHERE code RLIKE BINARY '^[A-Z]+[a-z]{3}$';

You would replace "table1" and "code" with appropriate values.

Of course, this isn't really generally applicable. I haven't written a
statement which replaces the first lower case letter. Instead, I've
written something that has the same effect because I know (i.e. you told
me) that the first lower case letter is in only one of two positions.

Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: How to do case sensitive replace with wild card matching?

am 25.12.2003 15:55:06 von Chris W

Michael Stassen wrote:
>
> mos wrote:
>
>> Ok, put your thinking caps on because this one bit of a toughie.
>>
>> I an Update statement that will insert a '/' in front of the *first*
>> lowercase letter of a field value.
>> Example: "ABCDef" becomes "ABCD/ef".
>>
>> Of course it doesn't always end in "ef" and could be any 2 or 3 group
>> of letters like "ABcde" becomes "AB/cde".
>>
>> Any idea on how to do this? Or should I do it manually?
>>
>> TIA
>>
>> Mike
>
>
> I assume you've already made sure that the column with the letters is
> wide enough to hold an additional character.
>
> Not sure what you mean by manually. I'd probably do it in perl, as it's
> almost a one-liner ($field =~ s/([a..z])/\$1/). In mysql, though, the
> problem seems to be that while we can match with a regular expression,
> we can't replace with one. Hmm...

Being an official perl hater I don't know the exact syntax with out
looking it up, but I am sure perl can do the search and replace in one
line. Here is how you would do it if this column were lines of text in vi

:%s/[a-z]*$/\/&/

this is basically saying find a group of any number of lower case
letters at the end of the line and then replace it with a / and then the
string that matched the search and do this on every line. I'm not sure
why the $ needs to be there but it didn't work right with out it. Now
if I were to guess at the perl equivalent of this it would look
something like this.

$fielsd =~ s/[a-z]*$/\/$1/;


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: How to do case sensitive replace with wild card matching?

am 25.12.2003 16:57:36 von Michael Stassen

Chris W wrote:
> Michael Stassen wrote:
>
>>
>> mos wrote:
>>
>>> Ok, put your thinking caps on because this one bit of a toughie.
>>>
>>> I an Update statement that will insert a '/' in front of the *first*
>>> lowercase letter of a field value.
>>> Example: "ABCDef" becomes "ABCD/ef".
>>>
>>> Of course it doesn't always end in "ef" and could be any 2 or 3 group
>>> of letters like "ABcde" becomes "AB/cde".
>>>
>>> Any idea on how to do this? Or should I do it manually?
>>>
>>> TIA
>>>
>>> Mike
>>
>>
>> I assume you've already made sure that the column with the letters is
>> wide enough to hold an additional character.
>>
>> Not sure what you mean by manually. I'd probably do it in perl, as
>> it's almost a one-liner ($field =~ s/([a..z])/\$1/). In mysql,
>> though, the problem seems to be that while we can match with a regular
>> expression, we can't replace with one. Hmm...
>
>
> Being an official perl hater I don't know the exact syntax with out
> looking it up, but I am sure perl can do the search and replace in one
> line. Here is how you would do it if this column were lines of text in vi
>
> :%s/[a-z]*$/\/&/
>
> this is basically saying find a group of any number of lower case
> letters at the end of the line and then replace it with a / and then the
> string that matched the search and do this on every line. I'm not sure
> why the $ needs to be there but it didn't work right with out it. Now
> if I were to guess at the perl equivalent of this it would look
> something like this.
>
> $fielsd =~ s/[a-z]*$/\/$1/;

You are right that perl can do the search and replace in one line, but
you'll need some more code to read and write the data, either from mysql
or from a source file before importing into mysql. That's why I said
"almost" a one-liner.

You are also right that I got the perl line wrong. Considering how much
perl I use, that's embarassing. Sigh. I have to admit I tested the sql
I suggested but not the perl. I should have been more careful.

Your regular expression isn't quite right either, though. The * means
to match 0 or more, so [a-z]* will match the start of ANY string.
That's why you needed the $ at the end, as it anchors the search to
match at the end. Thus [a-z]*$ matches 0 or more lower case letters at
the end. This is fine if there are any lower case letters in the
string, but will put a / at the end of any string that doesn't contain a
lower case letter. For example, it will turn "ABCD" into "ABCD/". It
will also turn "ABcdEFg" into "ABcdEF/g". In other words, your version
puts a / after the last non-lower case letter, instead of in front of
the first lower case letter.

To find the first lower case letter, we need to look for [a-z]. So the
perl line should be

$field =~ s/([a-z])/\/$1/

or, better yet

$field =~ s|([a-z])|/$1|

In vi, then, this would be

:%s/[a-z]/\/&/

Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: How to do case sensitive replace with wild card matching?

am 26.12.2003 17:10:21 von Chris W

Michael Stassen wrote:

> You are right that perl can do the search and replace in one line, but
> you'll need some more code to read and write the data, either from <
> > mysql or from a source file before importing into mysql. That's
why I > said "almost" a one-liner.
>
> You are also right that I got the perl line wrong. Considering how
> much perl I use, that's embarassing. Sigh. I have to admit I tested
> the sql I suggested but not the perl. I should have been more careful.

I must have read your first post on this way too fast. I thought you
were saying that perl couldn't do the search and replace in one line.
After reading again I see that is not what you were saying.



>
> Your regular expression isn't quite right either, though. The * means
> to match 0 or more, so [a-z]* will match the start of ANY string.
> That's why you needed the $ at the end, as it anchors the search to
> match at the end. Thus [a-z]*$ matches 0 or more lower case letters at
> the end. This is fine if there are any lower case letters in the
> string, but will put a / at the end of any string that doesn't contain a
> lower case letter. For example, it will turn "ABCD" into "ABCD/". It
> will also turn "ABcdEFg" into "ABcdEF/g". In other words, your version
> puts a / after the last non-lower case letter, instead of in front of
> the first lower case letter.


Thanks for the heads up on why the [a-z]* was doing what it was. I
don't know why I didn't think of that.


> To find the first lower case letter, we need to look for [a-z]. So the
> perl line should be
>
> $field =~ s/([a-z])/\/$1/
>
> or, better yet
>
> $field =~ s|([a-z])|/$1|
>
> In vi, then, this would be
>
> :%s/[a-z]/\/&/

obviously your regexp will do what you are saying, but I'm not sure it
is a best fit for what the original poster described.

He said that there would be some number of upper case letters followed
with 2 or 3 lower case letters. He made no mention of there being any
possibility of a mix of lower and upper case anywhere else. He also
indicated that the lower case letters would always be at the end of the
string so I am thinking that if you were going to assume the possibility
of some other lower case letters in the sting you would not want to put
the / in front of the first one you saw but only in front of a trailing
2 or 3 lower case letters so I think my first suggestion would be a
better fit than the one you gust proposed. I also think the following
would be a good fit, or maybe better than my first, since my first would
place a / at the end of a line of all caps which shouldn't even exist if
the data is as described by the original poster.

:%s/[a-z][a-z][a-z]*$/\/&/

that would only put the / in there if as described there were 2 or 3
lower case letters at the end of the string.

I love arguing about regular expressions... but maybe not the best topic
for a MySQL list I guess we should take any further discussion on this
off the list.

Chris W


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: How to do case sensitive replace with wild card matching?

am 27.12.2003 06:58:36 von mos

Chris & Michael,
Thanks for the feedback. I was hoping it could be done in SQL without
using Perl since I don't have any experience with it. But I can use some
of the ideas you gave me to create a Delphi program quick enough. Thanks
again. :)

Mike



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: How to do case sensitive replace with wild card matching?

am 27.12.2003 07:44:19 von Michael Stassen

You're welcome. I did suggest some (not very elegant) SQL in my first
note. I take it that didn't turn out to be useful. It's possible that
if you were to describe why not, someone could make a better suggestion.
Of course, I suppose you may have already solved this in Delphi.

mos wrote:

> Chris & Michael,
> Thanks for the feedback. I was hoping it could be done in SQL
> without using Perl since I don't have any experience with it. But I can
> use some of the ideas you gave me to create a Delphi program quick
> enough. Thanks again. :)
>
> Mike
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: How to do case sensitive replace with wild card matching?

am 27.12.2003 19:40:41 von mos

At 12:44 AM 12/27/2003, Michael Stassen wrote:
>You're welcome. I did suggest some (not very elegant) SQL in my first
>note. I take it that didn't turn out to be useful. It's possible that if
>you were to describe why not, someone could make a better suggestion. Of
>course, I suppose you may have already solved this in Delphi.
>
>mos wrote:
>
>>Chris & Michael,
>> Thanks for the feedback. I was hoping it could be done in SQL
>> without using Perl since I don't have any experience with it. But I can
>> use some of the ideas you gave me to create a Delphi program quick
>> enough. Thanks again. :)
>>Mike
>>


Mike,
Thanks again for your insight. Re: your SQL solution:

>>Perhaps we can take advantage of what we know about your data. I will
pretend your table is named "table1" and your column with the letters is
named "code". If I understand you correctly, the code column has some
number of capital letters followed by 2 or 3 lower case letters. If that's
true, I think we could do this in 2 updates, like this:<<

Unfortunately there could be more than 2 or 3 lowercase letters. (That's
my fault for not explaining the scope of the problem more thoroughly).
Example: "Ab" or "Abcdfg" or "ABCDEFg" or "ABCDEFghijk" etc.

Instead of writing a Perl script which I don't know or have (but I have
used PHP though), I would find it easier to roll my own Delphi program to
do it. I'm a bit lazy and was hoping the MySQL functions could do it in SQL
only, but it looks like that's not possible. Thanks again for the effort. :-)

Mike



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org