Hexadecimal Strings

A hexadecimal string is an ordinary character string that obeys the following rules:

• each single character in the string is one of the following: '0' through '9', or 'A' through 'F', and

• the string contains pairs of characters; i.e., the string length is an even number.

In other words, a hexadecimal string is made up of pairs of characters '00' through 'FF'.

Hexadecimal strings are handy in SQL Anywhere because there are a lot of functions for handling strings and not much in the way of features for representing complex data structures; e.g., the contents of a JPEG file.

...and any arbitrary string of data, including that JPEG file, can be converted to a hexadecimal string by converting each successive byte value 0 through 255 to the corresponding hexadecimal string '00' through 'FF'.

Once upon a time, converting a LONG BINARY string to a hexadecimal LONG VARCHAR meant coding a funky (and slow) loop involving RIGHT, INTTOHEX, ASCII and SUBSTR function calls. Now, SQL Anywhere 11 lets you perform the conversion in one single, very fast, UNLOAD statement using the INTO VARIABLE and HEXADECIMAL ON clauses:

BEGIN
DECLARE @hex_string LONG VARCHAR;
UNLOAD
   SELECT xp_read_file ( 'boat.jpg' )  
   INTO VARIABLE @hex_string
   HEXADECIMAL ON;
SELECT SUBSTR ( @hex_string, 3 );
END;

Here's how it works:

• The xp_read_file function returns the entire contents of the boat.jpg file as a LONG BINARY result.

• The INTO VARIABLE clause tells the UNLOAD statement to put the output in the local LONG VARCHAR variable @hex_string instead of a file.

• The HEXADECIMAL ON clause (which is actually the default) tells UNLOAD to convert binary columns to strings '0xnnnn...' where '0x' is a fixed prefix and each nn is a pair of hexadecimal characters.

  (Doesn't that sound familiar?
  ...it's the definition of "hexadecimal string" given earlier :)

• The final SELECT SUBSTR strips the leading '0x' and displays the result.

Here's what the first 1000 characters look like, representing the first 500 bytes of boat.jpg:

         1         2         3         4         5
12345678901234567890123456789012345678901234567890
--------------------------------------------------
ffd8ffe000104a46494600010200006400640000ffe10d7168
7474703a2f2f6e732e61646f62652e636f6d2f7861702f312e
302f003c3f787061636b657420626567696e3d22efbbbf2220
69643d2257354d304d7043656869487a7265537a4e54637a6b
633964223f3e0a3c783a786d706d65746120786d6c6e733a78
3d2261646f62653a6e733a6d6574612f2220783a786d70746b
3d22332e312e322d313133223e0a203c7264663a5244462078
6d6c6e733a7264663d22687474703a2f2f7777772e77332e6f
72672f313939392f30322f32322d7264662d73796e7461782d
6e7323223e0a20203c7264663a4465736372697074696f6e20
7264663a61626f75743d22220a20202020786d6c6e733a7469
66663d22687474703a2f2f6e732e61646f62652e636f6d2f74
6966662f312e302f220a20202020786d6c6e733a70686f746f
73686f703d22687474703a2f2f6e732e61646f62652e636f6d
2f70686f746f73686f702f312e302f220a20202020786d6c6e
733a7861703d22687474703a2f2f6e732e61646f62652e636f
6d2f7861702f312e302f220a20202020786d6c6e733a657869
663d22687474703a2f2f6e732e61646f62652e636f6d2f6578
69662f312e302f220a202020746966663a496d616765576964
74683d22343437220a202020746966663a496d6167654c656e

Oh, yeah... here's what it really looks like: