replace query
This is a discussion on replace query within the MySQL Database forums, part of the Database Forums category; I been playing with this for awhile, but can't seem to successfully execute a simple replace query with MySQL ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-08-2007
|
|||
|
|||
replace query
I been playing with this for awhile, but can't seem to successfully execute
a simple replace query with MySQL 4.1.12 via phpMyAdmin 2.7.0-pl2 The table only contains around 650 records, yet the query just hangs. I came across this syntax example: update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]'); My real world usage is: UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\'); where the backslash is added after the 4-digit year. An example piece of fileloc data looks like "2001DOM-MK008-C.PDF". The column is defined as varchar(50). Any suggestions would be greatly appreciated. 
|
|
02-08-2007
|
|||
|
|||
|
Re: replace query
On 8 Feb, 03:38, "Bosconian" <nob...@nowhere.com> wrote:
> I been playing with this for awhile, but can't seem to successfully execute > a simple replace query with MySQL 4.1.12 via phpMyAdmin 2.7.0-pl2 > > The table only contains around 650 records, yet the query just hangs. > > I came across this syntax example: > > update [table_name] set [field_name] = > replace([field_name],'[string_to_find]','[string_to_replace]'); > > My real world usage is: > > UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\'); > > where the backslash is added after the 4-digit year. > > An example piece of fileloc data looks like "2001DOM-MK008-C.PDF". The > column is defined as varchar(50). > > Any suggestions would be greatly appreciated. Try doubling up your backslash thus: UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\\');
|
|
02-08-2007
|
|||
|
|||
|
Re: replace query
"Bosconian" <nobody@nowhere.com> wrote in message news:9q-dneNpi8O2BlfYnZ2dnUVZ_smonZ2d@comcast.com... >I been playing with this for awhile, but can't seem to successfully execute >a simple replace query with MySQL 4.1.12 via phpMyAdmin 2.7.0-pl2 > > The table only contains around 650 records, yet the query just hangs. > > I came across this syntax example: > > update [table_name] set [field_name] = > replace([field_name],'[string_to_find]','[string_to_replace]'); > > My real world usage is: > > UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\'); > > where the backslash is added after the 4-digit year. > > An example piece of fileloc data looks like "2001DOM-MK008-C.PDF". The > column is defined as varchar(50). > > Any suggestions would be greatly appreciated. > > You're getting into a neverending loop 2001DOM-MK008-C.PDF becomes 2001\DOM-MK008-C.PDF 2001\DOM-MK008-C.PDF becomes 2001\\DOM-MK008-C.PDF 2001\\DOM-MK008-C.PDF becomes 2001\\\DOM-MK008-C.PDF Try : UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','test\'); followed by UPDATE attachments SET fileloc = REPLACE(fileloc,'test\','2001\');
|
|
02-08-2007
|
|||
|
|||
|
Re: replace query
"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:1170926173.684834.299100@a34g2000cwb.googlegr oups.com... > On 8 Feb, 03:38, "Bosconian" <nob...@nowhere.com> wrote: >> I been playing with this for awhile, but can't seem to successfully >> execute >> a simple replace query with MySQL 4.1.12 via phpMyAdmin 2.7.0-pl2 >> >> The table only contains around 650 records, yet the query just hangs. >> >> I came across this syntax example: >> >> update [table_name] set [field_name] = >> replace([field_name],'[string_to_find]','[string_to_replace]'); >> >> My real world usage is: >> >> UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\'); >> >> where the backslash is added after the 4-digit year. >> >> An example piece of fileloc data looks like "2001DOM-MK008-C.PDF". The >> column is defined as varchar(50). >> >> Any suggestions would be greatly appreciated. > > Try doubling up your backslash thus: > UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\\'); > Yup, escaping the backslash did the trick. Shame on me for not thinking of it on my own. Thanks!
|
|
02-08-2007
|
|||
|
|||
|
Re: replace query
"Sean" <sean.anderson@[nospam]oakleafgroup.biz> wrote in message
news:1170927436.49459@kestrel.skynet.co.uk... > > "Bosconian" <nobody@nowhere.com> wrote in message > news:9q-dneNpi8O2BlfYnZ2dnUVZ_smonZ2d@comcast.com... >>I been playing with this for awhile, but can't seem to successfully >>execute a simple replace query with MySQL 4.1.12 via phpMyAdmin 2.7.0-pl2 >> >> The table only contains around 650 records, yet the query just hangs. >> >> I came across this syntax example: >> >> update [table_name] set [field_name] = >> replace([field_name],'[string_to_find]','[string_to_replace]'); >> >> My real world usage is: >> >> UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\'); >> >> where the backslash is added after the 4-digit year. >> >> An example piece of fileloc data looks like "2001DOM-MK008-C.PDF". The >> column is defined as varchar(50). >> >> Any suggestions would be greatly appreciated. >> >> > > You're getting into a neverending loop > > 2001DOM-MK008-C.PDF becomes 2001\DOM-MK008-C.PDF > 2001\DOM-MK008-C.PDF becomes 2001\\DOM-MK008-C.PDF > 2001\\DOM-MK008-C.PDF becomes 2001\\\DOM-MK008-C.PDF > > Try : > > UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','test\'); > > followed by > > UPDATE attachments SET fileloc = REPLACE(fileloc,'test\','2001\'); > > > You might be right about the endless loop although no data ever changed (probably because the query never finished.) The solution in this case though was to simply escape the backslash.
|
Linear Mode
