CodingForums.com - View Single Post

Old Pedant · 02-17-2013, 01:21 AM

Easier than Bubikol's suggestion:

Code:

SELECT list, of, fields 
FROM youtable
ORDER BY CAST( SUBSTR( yourOrdField, 4 ) AS DECIMAL )

That assumes that all your values in that field indeed start with 'ORD' so we can ignore the first 3 characters.

If the values might start with other 3-letter sequences, and if you want the values in order, including the letters, as for example:

Code:

ABC331
ABC2017
DEF9
DEF1311
ORD771
ORD2468

Then do this:

Code:

SELECT list, of, fields 
FROM youtable
ORDER BY LEFT(yourOrdField,3), CAST( SUBSTR( yourOrdField, 4 ) AS DECIMAL )

02-17-2013, 01:21 AM	PM User \| #3
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,248 Thanks: 59 Thanked 3,999 Times in 3,968 Posts	Easier than Bubikol's suggestion: Code: SELECT list, of, fields FROM youtable ORDER BY CAST( SUBSTR( yourOrdField, 4 ) AS DECIMAL ) That assumes that all your values in that field indeed start with 'ORD' so we can ignore the first 3 characters. If the values might start with other 3-letter sequences, and if you want the values in order, including the letters, as for example: Code: ABC331 ABC2017 DEF9 DEF1311 ORD771 ORD2468 Then do this: Code: SELECT list, of, fields FROM youtable ORDER BY LEFT(yourOrdField,3), CAST( SUBSTR( yourOrdField, 4 ) AS DECIMAL ) __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is. Last edited by Old Pedant; 02-17-2013 at 01:26 AM..