Re: Oracle hexadecimal query
From: Urs Metzger <urs_at_ursmetzger.de>
Date: Fri, 07 Mar 2008 00:16:03 +0100
Message-ID: <fqptuj$25p$1@online.de>
>> nivS1978_at_gmail.com 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
>> tahiti.oracle.com 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
Date: Fri, 07 Mar 2008 00:16:03 +0100
Message-ID: <fqptuj$25p$1@online.de>
DA Morgan schrieb:
> Urs Metzger wrote:
>> nivS1978_at_gmail.com 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
>> tahiti.oracle.com 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');
NUM
31
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