homepage Welcome to WebmasterWorld Guest from 54.198.46.115
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL
Complex UPDATE I think?
Alternative Future




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

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




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved