5

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.)

Josh Hull
  • 1,723
  • 1
  • 16
  • 24

3 Answers3

3

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.

Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
  • That's a helpful start. So once the engine determines the correct binary value, it must put those bits directly into a memory location and addresses it as a raw datatype. Does that seem accurate? As opposed to encoding that value in the system characterset, and returning it as a varchar value of ones and zeros. That's what the BINARY function does in DB2 on z/OS. – Josh Hull Aug 29 '14 at 15:21
  • The way I interpret it, the HEXTORAW() function would take any arbitrary string of hexadecimal characters, convert it to binary and store it as binary in the RAW datatype. – Mark J. Bobak Aug 29 '14 at 15:33
3

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.)

Josh Hull
  • 1,723
  • 1
  • 16
  • 24
0

In addition to the algorithm given by Josh Hull, it's important to keep in mind that full bytes will be written in RAW format and not the individual bits as mentioned in the answer. This becomes important in the cases like this, when the HEXTORAW function gets called with an odd number of 1-9, A-F characters.

HEXTORAW('123')

In that case, if we apply the above algorithm, the RAW value would be:

0001 0010 0011

but this will not be the case, as it would mean having a byte and a half written in RAW binary. Instead, the whole two bytes will be written by adding the four leading zeros to the first half byte:

0000 0001 0010 0011

Essentially, if we group four bit groups into individual bytes, the resulting RAW binary will be:

00000001 00100011

In practice, this means the following. If the string passed to the HEXTORAW function has odd number of characters, to get the RAW binary, pad it with a leading zero and then apply the algorithm given by Josh.

HEXTORAW('123') => HEXTORAW('0123') => 00000001 00100011
ironcev
  • 382
  • 3
  • 15