Have you ever tried to order by a VARCHAR2 column that has numeric information in it? Your query is sorted ALPHABETICALLY instead of numerically. That is, your order is 1,10,2,20,200,3,4... instead of 1,2,3,4,10,20,200. If you try to use TO_NUMBER in the order_by your query blows up if there are any alpha characters in the column. This tip details a method that can be used to have the order_by return the columns numerically even if there are some alpha characters in the column. Consider the following table: SQL> desc NUMBER_SORT Name Null? Type ------------------- -------- ------------ sortby NOT NULL VARCHAR2(20) SQL> SELECT * from NUMBER_SORT SORTBY -------------------- 100 A 1 10 1AB 2 20 BBBB 1000 11 30 3 200 21 14 rows selected If you do a simple order_by your result will be: SQL> SELECT sortby 2 FROM Number_Sort 3 ORDER BY sortby; SORTBY -------------------- 1 10 100 1000 11 1AB 2 20 200 21 3 30 A BBBB 14 rows selected Which is not what you want! However, the following select WILL return the column ordered numerically: SQL>SELECT sortby 2 FROM Number_Sort 3 ORDER BY DECODE(TO_CHAR(NVL(LENGTH(TRANSLATE(sortby,'A1234567890','A')),0)),'0',LPAD(sortby,8),sortby) 4 / SORTBY -------------------- 1 2 3 10 11 20 21 30 100 200 1000 1AB A BBBB 14 rows selected. Now let's take that construct apart to see how it works: TRANSLATE(sortby,'A1234567890','A') - This usage of TRANSLATE strips all of the numeric characters (1..9) out of the value of sortby. For any row where sortby ONLY contains numeric characters, it returns a null string (''). NVL(LENGTH(....),0)) - This part determines the length of the TRANSLATEd string, and NVLs it to 0 if the string is null. The SQL construct up to this point will return 0 if sortby has only numeric characters, and will return a positive integer if there are any non-numeric characters. TO_CHAR(....) - Converts the number returned by the LENGTH into a varchar. This needs to be done so that the DECODE will work correctly. DECODE(....,'0',LPAD(sortby,8),sortby) - Now we come to the guts of this technique. This DECODE checks to see if the value is numeric only (a LENGTH of '0'), and if so, returns the value of sortby LPADed to 8 characters. If the value has any alpha characters (LENGTH > 0) it just returns sortby without any modification. Now why do we want to LPAD the numeric values only? Because space (' ') sorts BEFORE the numbers! This allows for a decimal place by decimal place comparison of the two numbers. The '1' will return with 7 leading blanks, the '10' with 6, ect. When the alphabetical sort is done, the values are compared, character by character, and blank sorts before the numbers. If you selected the SQL construct that you are sorting by, you would see something like: SQL> SELECT DECODE(TO_CHAR(NVL(LENGTH(TRANSLATE(sortby,'A1234567890','A')),0)),'0',LPAD(sortby,8),sortby) 2 FROM Number_Sort 3 ORDER BY DECODE(TO_CHAR(NVL(LENGTH(TRANSLATE(sortby,'A1234567890','A')),0)),'0',LPAD(sortby,8),sortby) 4 / SORTBY -------------------- 1 2 3 10 11 20 21 30 nbsp; 100 200 1000 1AB A BBBB 14 rows selected. The '8' I used in the LPAD is just arbitrary. If I had 15 digit numbers in the SORTBY column, I would use a value greater than 15 so that any number would sort correctly.