Home » SQL & PL/SQL » SQL & PL/SQL » How do I query a VARCHAR2 using hex notation ?
How do I query a VARCHAR2 using hex notation ? [message #125] Wed, 16 January 2002 09:06 Go to next message
Kelvin
Messages: 6
Registered: January 2002
Junior Member
I'd like to say:

SELECT * FROM MYTABLE WHERE MYTABLE.COLUMN >= 0x54555657

How is this done in Oracle, in SQL Server I do it within a CAST. Thanks.
Re: How do I query a VARCHAR2 using hex notation ? [message #135 is a reply to message #125] Thu, 17 January 2002 02:48 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
Hi,

can u tell me what is the character equivalent of 0x54555657. how do u get this char. from hex.

if i have got the problem then here is the solution:
---------------------------------------------
CREATE OR REPLACE FUNCTION hex2dec (hexnum in char) RETURN number IS
i number;
digits number;
result number := 0;
current_digit char(1);
current_digit_dec number;
BEGIN
digits := length(hexnum);
for i in 1..digits loop
current_digit := SUBSTR(hexnum, i, 1);
if current_digit in ('A','B','C','D','E','F') then
current_digit_dec := ascii(current_digit) - ascii('A') + 10;
else
current_digit_dec := to_number(current_digit);
end if;
result := (result * 16) + current_digit_dec;
end loop;
return result;
END hex2dec;
/

SELECT * FROM MYTABLE WHERE MYTABLE.COLUMN >=
to_char(hex2dec(54555657));
-----------------------------------

cheers
pratap
Re: How do I query a VARCHAR2 using hex notation ? [message #182 is a reply to message #125] Thu, 24 January 2002 07:54 Go to previous message
devi
Messages: 1
Registered: January 2002
Junior Member
No Message Body
Previous Topic: creating a procedure
Next Topic: Time Question
Goto Forum:
  


Current Time: Wed Nov 25 22:25:44 CST 2020