Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies


Complex UPDATE I think?


Alternative Future

1:24 pm on Mar 2, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

Greetings to the forum

I currently have a few thousand values in a column lets call it the product_url column the URLS look like this /directory/desc1-desc2-desc3-desc4.html directory, desc1, desc2 & desc3 never changes basically its the same throughout all the values. I need to replace the values in product_url with just the value of desc4 without the .html so instead of /directory/desc1-desc2-desc3-desc4.html I would just have desc4 Is this sort of command possible with SQL? /directory/desc1-desc2-desc3-desc4.htmleverything in bold goes







As you can see from my example the only thing that changes is desc4 all other values are identical to each other - I know it was very very bad db design, it was years ago over 12 I set up this and just didn't think about it :(




3:04 pm on Mar 2, 2010 (gmt 0)

10+ Year Member

You can check the MySQL documentation to find a wildcard/regexp syntax that would allow you to do it in one step. Regexp exists in MySQL, but I don't know the query syntax offhand. But here's one that will do it in two steps:

UPDATE `tblname` SET product_url = replace(product_url, '/directory/desc1-desc2-desc3-', '');

UPDATE `tblname` SET product_url = replace(product_url, '.html', '');

That will first replace all instances of /directory/desc1-desc2-desc3- with nothing. Then it will replace all instances of .html with nothing. Leaving you with just the 'mydesc', 'myproduct', 'myvalue' etc.

Back up first, of course.

Alternative Future

3:14 pm on Mar 2, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

Hi sonjay

Thanks so much for that - doing it in two stages works better than doing each one individual - you have saved me a huge amount of work :)

Thanks again


Featured Threads

Hot Threads This Week

Hot Threads This Month