How does the HEXTORAW() function work? What is the algorithm?

sql oracle algorithm rdbms db2-luw
Josh Hull picture Josh Hull · Aug 28, 2014 · Viewed 7.4k times · Source

HEXTORAW is a function found in several RDBMS's like Oracle, and DB2 on LUW. It takes a character, or integer input, and basically casts it to a HEX value. 

HEXTORAW(1234) = x'1234'

What is the algorithm for this type conversion? What is happening in the code behind the scenes?

(This is motivated by wanting to create this function in an RDBMS that does not have the HEXTORAW function.)

Answer

Mark J. Bobak picture Mark J. Bobak · Aug 28, 2014

From this page: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i46018

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as CB.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More