Zane Posted April 21, 2011 Share Posted April 21, 2011 I have one more MySQL situation at hand. This one involves taking off the trailing number of similar entries. Suppose, in my table... in some column.. lets say it's called SKU, I have the following entries. - SOSW SOSW1 SOSW2 SOSW3 SBIO SBIO1 SBIO2 SBIO3 Is there a quick query that I can use to change all those to SOSW SOSW SOSW SOSW SBIO SBIO SBIO SBIO Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/ Share on other sites More sharing options...
PFMaBiSmAd Posted April 21, 2011 Share Posted April 21, 2011 If you only want the left 4 characters - LEFT(your_column, 4) Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/#findComment-1204554 Share on other sites More sharing options...
Zane Posted April 21, 2011 Author Share Posted April 21, 2011 I wish I could just do that, but unfortunately some SKUs have 4 characters, some have 6, some 5, etc.. and that's excluding the trailing number. Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/#findComment-1204561 Share on other sites More sharing options...
Pikachu2000 Posted April 21, 2011 Share Posted April 21, 2011 Is it always just one trailing number, or could there be more? Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/#findComment-1204567 Share on other sites More sharing options...
Zane Posted April 21, 2011 Author Share Posted April 21, 2011 It's only one trailing number.. I made an admin panel for someone thinking an SKU was unique. So they simply added a number to the end of the SKU and griped about it later. He only got up to as high as 4 in most cases so it's not too complex. Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/#findComment-1204569 Share on other sites More sharing options...
PFMaBiSmAd Posted April 21, 2011 Share Posted April 21, 2011 The following link contains a generic - "remove all trailing numeric characters" solution - http://stackoverflow.com/questions/2568501/how-to-remove-digits-from-the-end-of-the-string-using-sql Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/#findComment-1204572 Share on other sites More sharing options...
Pikachu2000 Posted April 21, 2011 Share Posted April 21, 2011 If it's only one number, this should work. Might ought to test it before you just let it go wild, though SELECT REVERSE(SUBSTR(REVERSE(`field`), 2)) FROM `table` Or to update UPDATE `table` SET `field` = REVERSE(SUBSTR(REVERSE(`field`), 2)) Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/#findComment-1204575 Share on other sites More sharing options...
Zane Posted April 21, 2011 Author Share Posted April 21, 2011 That did it Pica, 'preciate it. I knew there had to be some kind of SQL trickery to fix that. Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/#findComment-1204577 Share on other sites More sharing options...
Pikachu2000 Posted April 21, 2011 Share Posted April 21, 2011 No problem, dawg. Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/#findComment-1204579 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.