Re: Easy regex replace?
On 2006-03-20, at 12:11, Pooly wrote: 2006/3/19, Adam i Agnieszka Gąsiorowski FNORD [EMAIL PROTECTED]: On 2006-03-18, at 00:59, Yani Copas wrote: Is there a quick and dirty way to update such that I can only affect the portion of a string (varchar column) that matches a regexp? (e.g. replace all '%20' with ' ' leaving the rest untouched?) You know that proverb - For a man in possession of a hammer, everything looks like a nail. Don't do that. MySQL is *really slow* with Regular Expressions. It will be much easier to SELECT all records you want to change, storing their IDs in a list (or array) construct, then tell your favourite script program to construct an REPLACE query out of these chosen few, after it does whatever you want it to do with the records' data. Yeah, but sometimes beoing able to do such things on the mysql command line would be very helpful ! (Instead of having a script for such simple things which would be like having a jack hammer for a nail.. ) Oh, in that case use a subselect syntax - inner one would SELECT FROM using REGEX syntax (use the fine manual, it's described in detail) for the outer one, wytch would create an UPDATE statement (SQL) using data from the inner one. Oh, add outermost one executing that statement, if you wish or simply copypaste the result of the previous cascade. -- Seks, seksić, seksolatki...uri: news:pl.soc.seks.moderowana Siúil, siúil, siúil A rúin...Siúil go sochair agus siúil go ciúin! Siúil go doras agus éalaigh liom! Is go dté tú mo mhúirnín slán... https://hyperreal.info | https://kanaba.info |= Szanuj Zieleń! smime.p7s Description: S/MIME cryptographic signature
Re: Easy regex replace?
2006/3/19, Adam i Agnieszka Gąsiorowski FNORD [EMAIL PROTECTED]: On 2006-03-18, at 00:59, Yani Copas wrote: Is there a quick and dirty way to update such that I can only affect the portion of a string (varchar column) that matches a regexp? (e.g. replace all '%20' with ' ' leaving the rest untouched?) You know that proverb - For a man in possession of a hammer, everything looks like a nail. Don't do that. MySQL is *really slow* with Regular Expressions. It will be much easier to SELECT all records you want to change, storing their IDs in a list (or array) construct, then tell your favourite script program to construct an REPLACE query out of these chosen few, after it does whatever you want it to do with the records' data. Yeah, but sometimes beoing able to do such things on the mysql command line would be very helpful ! (Instead of having a script for such simple things which would be like having a jack hammer for a nail.. ) -- Pooly Webzine Rock : http://www.w-fenec.org/
RE: Easy regex replace?
If %20 are the actual characters in the varchar column you shuld be able to do UPDATE table SETcolumn_name =REPLACE(column_name,'%20',' '); You might have to use REPLACE(column_name,'\%20',' '); to force MySQL to treat % as an actual value instead of a wild card. -Original Message- From: Pooly [mailto:[EMAIL PROTECTED] Sent: Monday, March 20, 2006 5:11 AM To: MySQL General Subject: Re: Easy regex replace? 2006/3/19, Adam i Agnieszka Gąsiorowski FNORD [EMAIL PROTECTED]: On 2006-03-18, at 00:59, Yani Copas wrote: Is there a quick and dirty way to update such that I can only affect the portion of a string (varchar column) that matches a regexp? (e.g. replace all '%20' with ' ' leaving the rest untouched?) You know that proverb - For a man in possession of a hammer, everything looks like a nail. Don't do that. MySQL is *really slow* with Regular Expressions. It will be much easier to SELECT all records you want to change, storing their IDs in a list (or array) construct, then tell your favourite script program to construct an REPLACE query out of these chosen few, after it does whatever you want it to do with the records' data. Yeah, but sometimes beoing able to do such things on the mysql command line would be very helpful ! (Instead of having a script for such simple things which would be like having a jack hammer for a nail.. ) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy regex replace?
On 2006-03-18, at 00:59, Yani Copas wrote: Is there a quick and dirty way to update such that I can only affect the portion of a string (varchar column) that matches a regexp? (e.g. replace all '%20' with ' ' leaving the rest untouched?) You know that proverb - For a man in possession of a hammer, everything looks like a nail. Don't do that. MySQL is *really slow* with Regular Expressions. It will be much easier to SELECT all records you want to change, storing their IDs in a list (or array) construct, then tell your favourite script program to construct an REPLACE query out of these chosen few, after it does whatever you want it to do with the records' data. Tcl is well known for excellent Regular Expression engine, but most of popular script languages (like PHP) have decent ones. You might write a stored procedure when it will work properly in production quality versions of MySQL (in script language) to make your database more isolated from presentation layer of your software system. For now, simply use script language for processing data and MySQL for storing and protecting it. If anyone would like to correct my assumptions toward current state of stored procedures in arbitrary language feature, feel free to correct us! -- Sanity check! Warning! This message was written under influence of drugs, music, women, air, water, fire, Earth, antimatter and and love. All the characters included are fictious to the point and any connections toward real persons are random refuse charm smime.p7s Description: S/MIME cryptographic signature
