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