MySQL

Complex UPDATE I think?

[Alternative Future]

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

[sonjay]

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]

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