jump to navigation

Order by ascii in Oracle July 11, 2006

Posted by maxmil in : Oracle , trackback

I needed to order a table num-alpha instead of alpha-num which is the Oracle default ordering. By num-alpha i just mean numbers before letters, for example 0,1,2,3,4,5,6,7,8,9,a,b,c,d…

My first intent was to use Oracles ascii function:

select idproducto from productos order by ASCII(idproducto);

However this returns a value based only on the first character of idproducto, in this way aa and az return the same value, so that was no good.

The solution that i have found is to do a binary sort using the NSLSORT function:

select idproducto from productos order by NLSSORT(idproducto, 'NLS_SORT=BINARY');

Voila!

Comments»

no comments yet - be the first?