I really appreciate that, but after talking with my supervisor he wants as
little dependence on non-standard UDF libraries as possible.

So I actually had to write a SP to do it. Don't think it would be that fast
being called on insert or anything, but it does get the job done for me
right now since I am only using it to barcode batches of records. I only
have to perform the operation once per import job, so the time is not a
factor.

It uses 2 helper SP's

CREATE PROCEDURE H_DEC_2_HEX (DEC_INPUT Integer) returns (HEX_OUTPUT
VarChar(9) CHARACTER SET ASCII) AS

DECLARE HIGHEST_POWER INTEGER; /* Highest Power of 16 that can
fit in DEC_INPUT */
DECLARE HEX_CHAR VARCHAR(1); /* The new Hexadecimal character
to be added to HEX_OUTPUT */
DECLARE HEX_VALUE INTEGER; /* The integer value of the
hexadecimal character */

BEGIN
/* Set Output Variable to NULL */
HEX_OUTPUT = NULL;
/* Keep looping till DEC_INPUT is less than 0 */
WHILE (DEC_INPUT >0) DO
BEGIN
SELECT RESULT FROM H_GET_HIGHEST_POWER(:DEC_INPUT) INTO
:HIGHEST_POWER;
HEX_VALUE = FLOOR(DEC_INPUT / FLOOR(DPOWER(16,HIGHEST_POWER)));
SELECT RESULT FROM H_CONVERT_HEX_VALUE_2_CHAR(:HEX_VALUE) INTO
:HEX_CHAR;
IF (STRLEN(HEX_OUTPUT) >=1) THEN
BEGIN
HEX_OUTPUT = HEX_OUTPUT || HEX_CHAR;
END
ELSE
BEGIN
HEX_OUTPUT = HEX_CHAR;
END
DEC_INPUT = DEC_INPUT - (HEX_VALUE * FLOOR(DPOWER(16,HIGHEST_POWER)));
END
SUSPEND;
EXIT;
WHEN ANY DO
BEGIN
HEX_OUTPUT = NULL;
END
END
update rdb$procedures set rdb$description = '' where rdb$procedure_name =
'H_DEC_2_HEX';

CREATE PROCEDURE H_GET_HIGHEST_POWER (DEC_INPUT Integer) returns (RESULT
Integer) AS

BEGIN
/* Since base 16 is all we are ever looking at, a look up is more
efficient than the math */
RESULT = NULL;
IF (DEC_INPUT = 1) THEN RESULT = 0;
IF (DEC_INPUT <= 16) THEN RESULT = 1;
IF (DEC_INPUT > 16 AND DEC_INPUT <= 4096 ) THEN RESULT = 2;
IF (DEC_INPUT > 4096 AND DEC_INPUT <= 65536 ) THEN RESULT = 3;
IF (DEC_INPUT > 65536 AND DEC_INPUT <= 1048576 ) THEN RESULT = 4;
IF (DEC_INPUT > 1048576 AND DEC_INPUT <= 16777216 ) THEN RESULT = 5;
IF (DEC_INPUT > 16777216 AND DEC_INPUT <= 268435456 ) THEN RESULT = 6;
IF (DEC_INPUT > 268435456 AND DEC_INPUT <= 4924967296 ) THEN RESULT = 7;
SUSPEND;
EXIT;
WHEN ANY DO
BEGIN
RESULT = NULL;
END
END
update rdb$procedures set rdb$description = '' where rdb$procedure_name =
'H_GET_HIGHEST_POWER';

CREATE PROCEDURE H_CONVERT_HEX_VALUE_2_CHAR (HEX_VALUE SmallInt) returns
(RESULT VarChar(1) CHARACTER SET ASCII) AS

BEGIN
IF (HEX_VALUE <=9) THEN RESULT = CAST(HEX_VALUE AS VARCHAR(1));
IF (HEX_VALUE >=10) THEN
BEGIN
IF (HEX_VALUE = 10) THEN RESULT = 'A';
IF (HEX_VALUE = 11) THEN RESULT = 'B';
IF (HEX_VALUE = 12) THEN RESULT = 'C';
IF (HEX_VALUE = 13) THEN RESULT = 'D';
IF (HEX_VALUE = 14) THEN RESULT = 'E';
IF (HEX_VALUE = 15) THEN RESULT = 'F';
END
SUSPEND;
END
update rdb$procedures set rdb$description = '' where rdb$procedure_name =
'H_CONVERT_HEX_VALUE_2_CHAR';

Kind of a pain in the rear, but needed for right now :)



-----Original Message-----
From: Kjell Rilbe [mailto:kjell.rilbe@...]
Sent: Friday, February 11, 2005 10:49 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Hex Conversion Function



Edwin A. Epstein, III wrote:

> I have been searching for a while here, does anybody know a UDF function
> that will convert decimal to hex? I am unable to write my own UDF's since

I

> have only used VB.

I guess I could write one for you, as could probably a lot of people
here. What exactly do you need? What's the source datatype? Integer? And
you want it formatted how? 0x00000000? 0x0000? $0000? Something else? Do
you need it to support nulls, and if so, in what way? The obvious is
null in => null out.

Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



Yahoo! Groups Links