July 10th, 2013, 12:25 AM
[Help] Update mysql table with replace function with regular expression
I have a table 'posts' with id,message fields. I want to update table 'posts'.
In field 'message', I want to replace text 'httpdomain.com/threads/xxx#post123456' by 'httpdomain.com/posts/123456'
How can I use SQL update with REPLACE function like below:
update posts set message=REPLACE(message, 'httpdomain.com/*#post[\d]+', 'httpdomain.com/posts/$1');
I try this, but it isn't correct
If this isn't correct, please tell me how to update all post in table 'posts'
Thank you very much
July 10th, 2013, 04:38 AM
You cannot do this with a simple query. The most obvious way is to select records one by one, replace contents and save the record back to the table. If you need to perform this operation every time a record is added it would be better to replace contents right before saving the record.
July 10th, 2013, 06:21 AM
you cannot do this with REGEX
you can do it in pieces, but only if the xxx is always the same, or at least always the same length