peterjc Posted February 28, 2011 Share Posted February 28, 2011 I have a column to store the following serial no. Serial No. aa/0001 bb/0005 cc/0002 dd/0004 ee/0003 Could someone help me how to sort it by ignoring the aphabet and slash infront (Example: ignore the : "aa/" while sorting) Example the returned result will be: aa/0001 cc/0002 ee/0003 dd/0004 bb/0005 Thank in advance Quote Link to comment https://forums.phpfreaks.com/topic/229099-mysql-sorting-ignoring-prefix/ Share on other sites More sharing options...
fenway Posted March 1, 2011 Share Posted March 1, 2011 Depends on how stable that formatting is. You can split by the slash, remove the first 3 characters, etc. Quote Link to comment https://forums.phpfreaks.com/topic/229099-mysql-sorting-ignoring-prefix/#findComment-1181063 Share on other sites More sharing options...
peterjc Posted March 1, 2011 Author Share Posted March 1, 2011 thankl for your reply, fenway Could you show me an example on how to split the first 3 character and sort it? I am not really good at advance SQL. thank. Quote Link to comment https://forums.phpfreaks.com/topic/229099-mysql-sorting-ignoring-prefix/#findComment-1181101 Share on other sites More sharing options...
fenway Posted March 1, 2011 Share Posted March 1, 2011 Use: SUBSTRING(columnName,3) Quote Link to comment https://forums.phpfreaks.com/topic/229099-mysql-sorting-ignoring-prefix/#findComment-1181261 Share on other sites More sharing options...
sandeepcr2 Posted March 1, 2011 Share Posted March 1, 2011 Hi, Think this will work SELECT serial_no FROM `table ` order by substring(serial_no,3) Sandeep Quote Link to comment https://forums.phpfreaks.com/topic/229099-mysql-sorting-ignoring-prefix/#findComment-1181266 Share on other sites More sharing options...
peterjc Posted March 3, 2011 Author Share Posted March 3, 2011 Thank for the help. Problem solved. Quote Link to comment https://forums.phpfreaks.com/topic/229099-mysql-sorting-ignoring-prefix/#findComment-1182240 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.