Re: Numeric sorting within a string
Brian Mansell wrote: This is totally possible... Chris say your table is test, and the column is test_col.. use the following, and if the number is always at the end.. and always has a space in front of it this will work: SELECT test_col, SUBSTRING_INDEX(test_col, '', 1) as test_col_str, SUBSTRING_INDEX(test_col, ' ', -1) + 0 as test_col_num FROM test ORDER BY test_col_str, test_col_num; Ah, hadn't thought of that one. Thanks both Brian and Peter for your responses :). Works like a charm! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Numeric sorting within a string
I'm wondering if there is a way to do a numeric sort when the number exists in a string. More clearly, take for example: Radius 1200 Radius 1500 Radius 1800 Radius 300 Radius 600 Radius 900 Being that character wise 1 is before 3, I'm wondering if there's a way through the database to achieve: Radius 300 Radius 600 Radius 900 Radius 1200 Radius 1500 Radius 1800 If not I'll try and handle it through PHP instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Numeric sorting within a string
This is totally possible... Chris say your table is test, and the column is test_col.. use the following, and if the number is always at the end.. and always has a space in front of it this will work: SELECT test_col, SUBSTRING_INDEX(test_col, '', 1) as test_col_str, SUBSTRING_INDEX(test_col, ' ', -1) + 0 as test_col_num FROM test ORDER BY test_col_str, test_col_num; good luck, --bemansell On 2/15/07, Chris White [EMAIL PROTECTED] wrote: I'm wondering if there is a way to do a numeric sort when the number exists in a string. More clearly, take for example: Radius 1200 Radius 1500 Radius 1800 Radius 300 Radius 600 Radius 900 Being that character wise 1 is before 3, I'm wondering if there's a way through the database to achieve: Radius 300 Radius 600 Radius 900 Radius 1200 Radius 1500 Radius 1800 If not I'll try and handle it through PHP instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
