RE: Character Field Length Function for Binary Data
----------
David Samson (dsamson@ndsisrael.com) wrote:
>Billy wrote to me to ask what this "0xa73123" syntax is. I guess this is
>from a previous, other database life. What I meant by that was a field
>with a value of "a73123" (hex). How can I get an SQL function to return
>"3" as the length of this field (i.e. 3 bytes worth of real data)?
Assuming that you have managed to insert the data as 3 characters into a
CHAR(x) or VARCHAR(x) column in the database, the LENGTH() function will
return the correct value, 3.
The hard part will be getting the data into the database. You could do it
in ESQL/C by creating a string which contains the three characters:
$char minibuffer[4] = "\\xA7\\x31\\x23";
$insert into whereever(somecolumn) values ($minibuffer);
Or you could interpret the string on the fly, somehow - the problem being
to know when to treat 0xA73123 as 8 characters and when to treat is as 3
characters. I'm sure there isn't a built-in way of getting that done
inside the engine. Short of a custom UDR in IUS (or UDO) - TLA's are
wonderful things, aren't they? -- your only chance is to write an SP, and
it would not be all that easy, even then. You'd have to invoke the SP on a
string containing the hex data, convert each pair of characters into a
(decimal) integer, and then use the ASCII function to convert it to a
character and append that to the output string.
Roughly as follows (but beware - untested code ahead):
CREATE PROCEDURE hex_digit(d CHAR(1)) RETURNING SMALLINT; IF (d == '0') THEN RETURN 0;
ELIF (d == '1') THEN RETURN 1;
ELIF (d == '2') THEN RETURN 2;
ELIF (d == '3') THEN RETURN 3;
ELIF (d == '4') THEN RETURN 4;
ELIF (d == '5') THEN RETURN 5;
ELIF (d == '6') THEN RETURN 6;
ELIF (d == '7') THEN RETURN 7;
ELIF (d == '8') THEN RETURN 8;
ELIF (d == '9') THEN RETURN 9;
ELIF (d == 'a' OR d == 'A') THEN RETURN 10;
ELIF (d == 'b' OR d == 'B') THEN RETURN 11;
ELIF (d == 'c' OR d == 'C') THEN RETURN 12;
ELIF (d == 'd' OR d == 'D') THEN RETURN 13;
ELIF (d == 'e' OR d == 'E') THEN RETURN 14;
ELIF (d == 'f' OR d == 'F') THEN RETURN 15;
ELSE RAISE EXCEPTION -746, -202, "Invalid hexadecimal digit in string in SP
hex_digit()";
END IF;
END PROCEDURE;
-- There might be a cleaner way of writing that, but I fear there probably
isn't.
CREATE PROCEDURE hexstring_to_ascii(in CHAR(256)) RETURNING VARCHAR(128); DEFINE out VARCHAR(128);
DEFINE len INTEGER;
DEFINE i INTEGER;
LET out = "";
WHILE (len >= 2)
LET I = hex_digit(in[1]) * 16 + hex_digit(in[2]);
LET out = out || ASCII(I);
LET in = in[3,256];
LET len = len - 2;
END WHILE;
RETURN out;
END PROCEDURE;
This code ignores any odd characters and bombs out with an error if you
have invalid hex data in the input. You can't invoke an SP in the values
list of an INSERT statement, so inserting the data into the database will
be interesting.
Yours,
Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>