homepage Welcome to WebmasterWorld Guest from 54.227.160.102
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, Moderator: open

Databases Forum

    
MySQL
Complex UPDATE I think?
Alternative Future

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4089930 posted 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

10+ Year Member



 
Msg#: 4089930 posted 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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4089930 posted 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