Re: Oracle hexadecimal query

From: Urs Metzger <>
Date: Fri, 07 Mar 2008 00:16:03 +0100
Message-ID: <fqptuj$25p$>

DA Morgan schrieb:

> Urs Metzger wrote:

>> schrieb:
>>> A quick and simple question.
>>> If I have a query like:
>>> SELECT * FROM table NUM = 31
>>> Now for reasons irelevant to my question the number provided is
>>> hexadecimal. I could ofcourse in the program convert this to a number
>>> but is there a syntax in oracle to use similar to the sql server:
>>> SELECT * FROM table NUM = 0x1F
>>> How do I write this in oracle?
>>> I know I can do a to_num('1F','XX') or something like that but is
>>> there a way to just write a hexadecimal number directly?
>>> Regards Hans Milling...
>> Hans,
>> there is no way to express a number in hexadecimal format directly.
>> This has not changed between at least 8.1.7 and 11.1.
>> Search the SQL Language Reference provided on
>> for "Literals".
>> Of course there is no need to convert the table data to hexadecimal,
>> but you do have to convert the hexadecimal representation '1F'
>> to the type in your table.
>> HtH,
>> Urs Metzger
> I would suspect you would create a user-defined data type and use
> it to build a table that would do this: But why?

As the op clearly states, NUM is a numeric (integer) column, so I would convert the query parameter '1F' into a number using TO_NUMBER (what else?).

SQL> create table t (num integer);

Tabelle wurde erstellt.

SQL> insert into t values(31);

1 Zeile wurde erstellt.

SQL> select * from t where num = to_number('1F', 'XX');



No need to "convert the table data to hexadecimal".

It's that simple.

Urs Metzger Received on Thu Mar 06 2008 - 17:16:03 CST

Original text of this message