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.