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/[EMAIL PROTECTED]
