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