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

Question

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 1

In order to have a complete algorithm here:

Given a character string as an input parameter

1.Validate that the character string contains only the numbers 1-9 or the letters A-F.

2.Calculate the binary value by iterating over each character, and concatenating the corresponding binary value:

 binary    hexadecimal
 0000      0
 0001      1
 0010      2
 0011      3
 0100      4
 0101      5
 0110      6
 0111      7
 1000      8
 1001      9
 1010      a  
 1011      b
 1100      c  
 1101      d  
 1110      e  
 1111      f  

For example, 1234 would be:

0001 0010 0011 0100

3.Using that value, set the bits of a memory location.

4.Address it as a raw datatype

5.Return it as the function return value

The resulting raw datatype will have the hex representation equivalent to the original string.

Given the input '1234' the function would return the raw datatype which would be displayed as the hex value x'1234'. Binary data is typically represented in HEX to make it easier to read and reference.

(This builds on Mark J. Bobak's answer, so I want to give credit to him, but I also wanted to post a complete procedure.)

Answer 2

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.