Sort Hex value

[Torsten Knauft]

Hi,

I've got a varchar2-column that contains a hexadecimal number (0-9, A-F).

Using "order by", Oracle sorts it like this:

A10
BFF
010
900

I would like it to be sorted in the natural order, like this:

010
900
A10
BFF

Any ideas?

Torsten

[Campbell Ritchie]

Welcome to JavaRanch

But that is the natural order. If it is a varchar, then it is a type of "text" and "text" is sorted in alphabetical order like that. Suggest you download them with JDBC, parse them to Integer (or Long or BigInteger) objects with a radix of 0x10, put them in an array, List<Number> or similar, then sort them.

Is there a built-in function in SQL which parses text into numbers? I don't remember seeing it. It is worthwhile going through the Oracle handbooks to see whether there is a special PLSQL or Oracle function like that.

[Jeanne Boyarsky]

Torsten,
TO_NUMBER can convert a hex string to a number and TO_CHAR can convert a number to hex. See this converter list for an example.

You don't really need to convert it back to hex for the sort though.

[sowmy raj]

TO_NUMBER( your_column, 'XXX' )