I currently have a VARCHAR2 column in an Oracle 8.1.7 database that I need to perform an alphanumeric sort on. We currently have page numbers stored in this column. The column data needs to be changed to allow decimal place page numbers and pages with alpha character. The page number have to be sorted like this:
1 1a 1.1 1.11a 2.1 2.2b 2.2111b 3 3.05 3.5 4a 4.1
Quick guide to this expert response:
- Multi-column solution
- Single-column solution
- "Dewey decimal"
The table definition includes something like:
, page_num VARCHAR2 (20)
With this solution, sorting is really complicated:
ORDER BY TRANSLATE ( page_text -- minus letters , '-abcdefghijklmnopqrstuvwxyz' , '-' ) , TRANSLATE ( page_text -- minus numerals and dot , '-0123456789.' , '-' );
This relies on the trick that TRANSLATE (s, sub_a, sub_b) will delete characters found the end of sub_a that do not have a counterpart in sub_b. (In other words, if a, the length of sub_a is greater than b, the length of sub_b, then the last a-b characters in sub_a will be deleted.) Since TRANSLATE requires three arguments, and the third one can't be NULL, you need a dummy ('-' in this example) that "translates" a character to itself.
Since you'll be doing the same TRANSLATEs every time you have to sort, you should write a view that computes the TRANSLATEd values, or a PL/SQL function that, when passed a page_num like '2.111a', returns a string suitable for sorting, such as '0000000002.1110000000a'.
Dewey Decimal digression
If anyone knows a better name for these tuples of integers, please let me know. "Dewey Decimal" is the only name I've heard, but it is misleading because it has nothing to do with the library classification system of the same name, which is, moreover, a true decimal number.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.