Welcome to WebmasterWorld Guest from 23.22.46.195

Forum Moderators: open

MySQL

Complex UPDATE I think?

   
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
/directory/desc1-desc2-desc3-mydesc.html

/directory/desc1-desc2-desc3-myproduct.html

/directory/desc1-desc2-desc3-myvalue.html

/directory/desc1-desc2-desc3-myentry.html

/directory/desc1-desc2-desc3-myclub.html

/directory/desc1-desc2-desc3-mypath.html

/directory/desc1-desc2-desc3-myhome.html

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 :(

TIA

-Gs
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.
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

-Gs
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month