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"
Your page "numbers" are becoming multi-part objects: you should store them in the table as a multi-part object, either a user-defined type or as several columns.
Columns are like atoms: it takes some work to combine atoms into molecules, but that's not nearly as difficult as splitting them to get their component particles. In the same way, the coding effort and execution time needed to combine small columns into larger objects is less than that need to parse and break down large columns.
Assumptions about the problem
In the sample data, "3.05" is shown as distinct from (and sorted before) "3.5", so I assume the numeric part is a true decimal, and that pages "1", "1.0" and "1.0000" are the same. Make sure all your users know this: the "Dewey decimal" system (where page "1.9" comes before "1.10", which is distinct from "1.100") may be more familiar. If the page numbers were Dewey decimals, I would recommend storing them in three columns: one integer for the numeric part before the decimal point, another integer for the numeric part after the decimal point, and an alphabetic part. But since, in this case, the numbers are true decimals, you can use two columns: a number and a string.
The table definition should include something like:
, page_num NUMBER (16,8) , page_letter CHAR (1)
With this solution, sorting is really easy:
ORDER BY page_num , page_letter
To display the combined page number concisely:
SELECT TRIM (TO_CHAR (page_num)) || page_letter AS page_number ...
With this solution, you get a lot of validation for free. For example, bogus page numbers like "1.2.3" and "b1" simply won't go into the columns. Also, if a user does enter a page number like "1.0000" instead of "1", it won't matter: Oracle stores them the same way.