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 Quote 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) Quote 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. Quote 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? Quote 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. Quote 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 Quote 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)) Quote 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. Quote 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. Quote Link to comment https://forums.phpfreaks.com/topic/234371-remove-the-likes/#findComment-1204579 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.