Order APEX column based on hidden data

An occasional question in the forums relates to issues ordering a particular column. It's one of those things that will probably keep coming up, so it's worth having another reference out here on the web.

The basic example stems from the need to order data that might contain characters.

with data as
  (select '1'  vc from dual union all
   select '11' vc from dual union all
   select '2'  vc from dual union all
   select 'a'  vc from dual)
select vc
from data
order by vc;

VC

--
1
11
2
a

Most of the time people would want this ordered numerically as 1,2,11, then characters before or after the numerics. There are a few SQL solutions, but we need something for APEX so when you click on the column heading - it orders as you expect.

Other sample use-cases might also be found

• in ealier versions of APEX where LOV sourced column order by return value, not display value
• any place where data is formatted in some way
• any occasion where you want to order data conditionally

So another example is the blob file size supplied format mask mentioned in last week's post. Here data was ordered by data values - not logically from our human perspective.
1KB
1MB
2KB

One solution offered here by Arie Geller is to prefix the column value with a hidden column
'<input type="hidden" value="'||(estimate-actual)||'" />'||abs(estimate - actual) AS variance
I found another example from Denes Kubicek back in 2008.

The solution can be even simpler & lightweight than that - use HTML comments as a prefix.

And typically we'll need to left-pad our character data to the same length - this ensures ordering compares apples to apples.

I've applied this prefix on my blob file size format mask example, to allow ordering on the column I said wouldn't order so well. The prefix is essentially the untrimmed output of filesize_mask(), with the leading spaces replaced with zeroes - and the suffix removed.

'<!--'||LPAD(dbms_lob.getlength(blob_content),10,'0')||'-->' 

||TRIM(apex_util.filesize_mask(dbms_lob.getlength(blob_content)))

Both solutions require the column to be displayed as "Standard Report Column". So if you are using text data it's best to also escape your content in the query using apex_escape.html()

Run the demo to see it in action. Try ordering using "Size Mask" vs "Bad Order" and see where +1MB files go

The file names are scrambled using much simplified scrambling technique demonstrated here by Joe Lipman.
DBMS_RANDOM.STRING('a',length(filename))
My full SQL can be seen at the bottom of the demo page.

-- Update: Be sure to read the comments on the post regarding Patrick Wolf's elegant solution of just putting the display version of the column in the column's HTML Expression.