Problem solve Get help with specific problems with your technologies, process and projects.

Alphanumeric page numbers, part 1

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.

    I'll give both a multi-column solution (which I recommend), and a single-column solution that will work in case you're already committed to storing the "numbers" in one big string.

    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.  

    Multi-column solution

    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.

    Click for page 2.

Dig Deeper on Using Oracle PL-SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.