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
'<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.
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.
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.